Hello team,
I wonder if it is possible to create 3 dependent combo boxes with filter without using VBA?
Regards,
Bita
3 dependent combo boxes with filter non-vba approach
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
-
- Administrator
- Posts: 78379
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: 3 dependent combo boxes with filter non-vba approach
Please provide more detailed information about what you want.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: 3 dependent combo boxes with filter non-vba approach
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
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
-
- Administrator
- Posts: 78379
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: 3 dependent combo boxes with filter non-vba approach
See the attached example. The Data Validation rules use a combination of OFFSET, MATCH and COUNTIF/COUNTIFS.
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: 3 dependent combo boxes with filter non-vba approach
Hans,
Thanks for response!
That works.
Can we use combo box instead of dropdowns?
Regards,
Thanks for response!
That works.
Can we use combo box instead of dropdowns?
Regards,
-
- Administrator
- Posts: 78379
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: 3 dependent combo boxes with filter non-vba approach
That would require VBA. You asked for a solution without VBA.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: 3 dependent combo boxes with filter non-vba approach
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
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.
-
- Administrator
- Posts: 78379
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: 3 dependent combo boxes with filter non-vba approach
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.
=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.
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: 3 dependent combo boxes with filter non-vba approach
Many, many thanks,
I will get back to you on this.
Bita
I will get back to you on this.
Bita