How to create 3 dependent List boxes for reporting?

BittenApple
BronzeLounger
Posts: 1407
Joined: 01 Mar 2015, 02:03

How to create 3 dependent List boxes for reporting?

Post by BittenApple »

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
-
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 71983
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How to create 3 dependent List boxes for reporting?

Post by HansV »

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.

dependent.xlsx
You do not have the required permissions to view the files attached to this post.
Regards,
Hans

BittenApple
BronzeLounger
Posts: 1407
Joined: 01 Mar 2015, 02:03

Re: How to create 3 dependent List boxes for reporting?

Post by BittenApple »

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

User avatar
HansV
Administrator
Posts: 71983
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How to create 3 dependent List boxes for reporting?

Post by HansV »

I'm afraid I don't understand any of your questions. Please explain in detail.
Regards,
Hans

BittenApple
BronzeLounger
Posts: 1407
Joined: 01 Mar 2015, 02:03

Re: How to create 3 dependent List boxes for reporting?

Post by BittenApple »

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

User avatar
HansV
Administrator
Posts: 71983
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How to create 3 dependent List boxes for reporting?

Post by HansV »

Where are the dropdowns? In your sample workbook there are no dropdowns.
Regards,
Hans

BittenApple
BronzeLounger
Posts: 1407
Joined: 01 Mar 2015, 02:03

Re: How to create 3 dependent List boxes for reporting?

Post by BittenApple »

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

User avatar
HansV
Administrator
Posts: 71983
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How to create 3 dependent List boxes for reporting?

Post by HansV »

S0421.png

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.
Regards,
Hans

BittenApple
BronzeLounger
Posts: 1407
Joined: 01 Mar 2015, 02:03

Re: How to create 3 dependent List boxes for reporting?

Post by BittenApple »

Thanks!
I took a different approach and it worked.
Respectfully,
Bitten

User avatar
HansV
Administrator
Posts: 71983
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How to create 3 dependent List boxes for reporting?

Post by HansV »

Here is an example that shows how to create three levels of data validation, with spaces in the items.

DataValidation.xlsx
You do not have the required permissions to view the files attached to this post.
Regards,
Hans

BittenApple
BronzeLounger
Posts: 1407
Joined: 01 Mar 2015, 02:03

Re: How to create 3 dependent List boxes for reporting?

Post by BittenApple »

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

BittenApple
BronzeLounger
Posts: 1407
Joined: 01 Mar 2015, 02:03

Re: How to create 3 dependent List boxes for reporting?

Post by BittenApple »

Hans,
I am not able to find my previous posts. I don't why.
Regards,
Blueblue

User avatar
HansV
Administrator
Posts: 71983
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How to create 3 dependent List boxes for reporting?

Post by HansV »

Regards,
Hans

BittenApple
BronzeLounger
Posts: 1407
Joined: 01 Mar 2015, 02:03

Re: How to create 3 dependent List boxes for reporting?

Post by BittenApple »

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