3 dependent combo boxes with filter non-vba approach

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

3 dependent combo boxes with filter non-vba approach

Post by BittenApple »

Hello team,
I wonder if it is possible to create 3 dependent combo boxes with filter without using VBA?
Regards,
Bita :thankyou: :thankyou: :thankyou: :thankyou: :hairout: :hairout: :hairout:

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

Re: 3 dependent combo boxes with filter non-vba approach

Post by HansV »

Please provide more detailed information about what you want.
Regards,
Hans

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

Re: 3 dependent combo boxes with filter non-vba approach

Post by BittenApple »

Hello Hans,
I need 3 combo boxes, which the second one is dependent on the first one and third one is dependent on the second one.
Please let me know if this is not clear.
Regards,
Bita

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

Re: 3 dependent combo boxes with filter non-vba approach

Post by HansV »

See the attached example. The Data Validation rules use a combination of OFFSET, MATCH and COUNTIF/COUNTIFS.

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

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

Re: 3 dependent combo boxes with filter non-vba approach

Post by BittenApple »

Hans,
Thanks for response!
That works.
Can we use combo box instead of dropdowns?
Regards,

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

Re: 3 dependent combo boxes with filter non-vba approach

Post by HansV »

That would require VBA. You asked for a solution without VBA.
Regards,
Hans

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

Re: 3 dependent combo boxes with filter non-vba approach

Post by BittenApple »

Hello Hans,

1-For offset function you have this:
OFFSET(Lists!$D$1,MATCH(B1,Lists!$C:$C,0)-1,0,COUNTIF(Lists!$C:$C,B1),1)
I want to evaluate this formula and it gives me #value error. I assume that the result is two values and can't fit into one cell.

Can you please explain about this? How can one validate this function?

2-Town dropdown has to display two entries:
Lake Oswego
Portland
It only shows Portland.


Regards,
Apple
Last edited by BittenApple on 16 Jun 2021, 05:11, edited 1 time in total.

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

Re: 3 dependent combo boxes with filter non-vba approach

Post by HansV »

1 - if you select two cells below each other, and enter the formula

=OFFSET(Lists!$D$1,MATCH(B1,Lists!$C:$C,0)-1,0,COUNTIF(Lists!$C:$C,B1),1)

confirmed with Ctrl+Shift+Enter, you should see the counties for the selected state.

2 - In the attached version, I have created three defined names: States, Counties and Towns, and used those in the data validation rules.
I have also added some cells with formulas that demonstrate how the counties and towns change as you select values in B1 and B2.

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

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

Re: 3 dependent combo boxes with filter non-vba approach

Post by BittenApple »

Many, many thanks,
I will get back to you on this.
Bita