How to create 3 dependent List boxes for reporting?
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
How to create 3 dependent List boxes for reporting?
Hello team,
I need to create 3 dependent list boxes.
I wonder what approach to take: 1-List from Data Validation 2-Listbox 3-comboxes
First listbox has two hospitals.
Second listbox has carriers but each hospital has got its own carriers, each hospital has 7 or 8 carriers, when the end user selects either hospital, each hospital maps to its own carriers. In term of diagnosis codes: diagnosis codes are same for both hospitals and all carriers
What approach should I take?
Or
I can create two radio button for the first drop down that has two choice only and for the rest, two drop down can work.
Regards,
Regards,
BittenApple
-
I need to create 3 dependent list boxes.
I wonder what approach to take: 1-List from Data Validation 2-Listbox 3-comboxes
First listbox has two hospitals.
Second listbox has carriers but each hospital has got its own carriers, each hospital has 7 or 8 carriers, when the end user selects either hospital, each hospital maps to its own carriers. In term of diagnosis codes: diagnosis codes are same for both hospitals and all carriers
What approach should I take?
Or
I can create two radio button for the first drop down that has two choice only and for the rest, two drop down can work.
Regards,
Regards,
BittenApple
-
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: How to create 3 dependent List boxes for reporting?
See the attached workbook.
You will have to modify it for your situation.
I have created four named ranges. You can see and change their definition in Formulas > Name Manager.
And the data validation for the Carrier column uses a formula that depends on the names of the hospitals.
You will have to modify it for your situation.
I have created four named ranges. You can see and change their definition in Formulas > Name Manager.
And the data validation for the Carrier column uses a formula that depends on the names of the hospitals.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: How to create 3 dependent List boxes for reporting?
Hello Hans,
I am so grateful for your response.
Hans/ team,
1-How do I capture the content of these 3 drop-downs and pull data from the table?
When the end user selects from the drop-downs, he/ she needs to see data for these headers:
diagocode num1 num2 num3.
2-We need to control NA, if a user selects values from drop-downs, the combination of the values should be available in the
table, so we can pull values for diagocode num1 num2 num3?
3-Is SumProduct a good idea?
4-My data/ table has many rows. Will the content of these columns: diagocode num1 num2 num3 be updated As the user selects from drop-downs?
5-Will formulas be updated automatically as we select different entries from drop-downs?
I appreciate your help on this!!!
BittenApple
I am so grateful for your response.
Hans/ team,
1-How do I capture the content of these 3 drop-downs and pull data from the table?
When the end user selects from the drop-downs, he/ she needs to see data for these headers:
diagocode num1 num2 num3.
2-We need to control NA, if a user selects values from drop-downs, the combination of the values should be available in the
table, so we can pull values for diagocode num1 num2 num3?
3-Is SumProduct a good idea?
4-My data/ table has many rows. Will the content of these columns: diagocode num1 num2 num3 be updated As the user selects from drop-downs?
5-Will formulas be updated automatically as we select different entries from drop-downs?
I appreciate your help on this!!!
BittenApple
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: How to create 3 dependent List boxes for reporting?
I'm afraid I don't understand any of your questions. Please explain in detail.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: How to create 3 dependent List boxes for reporting?
Hello Hans,
Let me break it down and sorry for my language.
I want the end user selects from the 3 drop-downs and then the content for: diagocode, num1, num2, num3, are looked up and their content are displayed.
Please let me know if this doesn't make sense.
Respectfully,
Bittenapple
Let me break it down and sorry for my language.
I want the end user selects from the 3 drop-downs and then the content for: diagocode, num1, num2, num3, are looked up and their content are displayed.
Please let me know if this doesn't make sense.
Respectfully,
Bittenapple
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: How to create 3 dependent List boxes for reporting?
Where are the dropdowns? In your sample workbook there are no dropdowns.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: How to create 3 dependent List boxes for reporting?
Hello Hans,
The dropdowns are in Dashboard tab. I need to add it to this workbook. Please let me take care of them and send it to you.
By the way, I didn't see what formula you have put for data validation. I select CarrierA and go to validation dialog box, it doesn't show what function you have put it.
Regards,
BittenApple
The dropdowns are in Dashboard tab. I need to add it to this workbook. Please let me take care of them and send it to you.
By the way, I didn't see what formula you have put for data validation. I select CarrierA and go to validation dialog box, it doesn't show what function you have put it.
Regards,
BittenApple
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: How to create 3 dependent List boxes for reporting?
The formula is
=IF($D4="Hospital A",CarrierA,IF($D4="Hospital B",CarrierB,""))
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: How to create 3 dependent List boxes for reporting?
Thanks!
I took a different approach and it worked.
Respectfully,
Bitten
I took a different approach and it worked.
Respectfully,
Bitten
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: How to create 3 dependent List boxes for reporting?
Here is an example that shows how to create three levels of data validation, with spaces in the items.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: How to create 3 dependent List boxes for reporting?
Hello Hans,
Thanks for your workbook. After viewing your workbook in a calm mode, I understood where my problem was. I was not putting a dollar sign for a cell in data validation box for function indirect(substitute(c2, " ", "_"). I can't believe how that costs me 48 hours of my life.
I worked continuously on that and I was not able to fix it. I am sure without your workbook, I was not able to get that to work.
In one version of my workbook by placing = sign, the third dropdown worked.
Regards,
BlueBlue
Thanks for your workbook. After viewing your workbook in a calm mode, I understood where my problem was. I was not putting a dollar sign for a cell in data validation box for function indirect(substitute(c2, " ", "_"). I can't believe how that costs me 48 hours of my life.
I worked continuously on that and I was not able to fix it. I am sure without your workbook, I was not able to get that to work.
In one version of my workbook by placing = sign, the third dropdown worked.
Regards,
BlueBlue
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: How to create 3 dependent List boxes for reporting?
Hans,
I am not able to find my previous posts. I don't why.
Regards,
Blueblue
I am not able to find my previous posts. I don't why.
Regards,
Blueblue
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: How to create 3 dependent List boxes for reporting?
Thanks,
That piece worked for me. I was not putting a dollar sign. As soon as I did, it worked. I was watching many videos and non of them had a dollar sign in the indirect(substitute(A1....)
Thanks a lot.
Regards,
Bita
That piece worked for me. I was not putting a dollar sign. As soon as I did, it worked. I was watching many videos and non of them had a dollar sign in the indirect(substitute(A1....)
Thanks a lot.
Regards,
Bita