Dependent searchable drop down list

[akankshakhare]

Hi,

I want data validation in column C searchable for eg if I write "f" then I get list of Coffee products etc.

Sample file is attached for reference.


If VBA is reqd pls let me know the code also.

 

[navic]

Maybe this video can help you.

See attached solution
Regards

 

[akankshakhare]

Hi, Thanks for replying.

I have checked this video, this works only one have independent list.

The file u have share, in that if you select pages ( in cell B4) then also it shows the sub category of products only.


I want that if i select pages as my main category then on searching it shows the category of pages only.

By using Offset formula in Data validation, I am not able to relate the sub category with main category.

 

[bosco_yip]

Hi, Thanks for replying.

I have checked this video, this works only one have independent list.

The file u have share, in that if you select pages ( in cell B4) then also it shows the sub category of products only.


I want that if i select pages as my main category then on searching it shows the category of pages only.

By using Offset formula in Data validation, I am not able to relate the sub category with main category.

Just modified navic's formulas, as in.....

1] In "helper1" H3, formula copied down :

=IF(ISNUMBER(SEARCH(LOOKUP("zz",$C:$C),OFFSET($H3,0,MATCH(LOOKUP("zz",$B:$B),$I$2:$K$2,0)))),MAX($H$2:H2)+1,"")

2] In "helper2" M3, formula copied down :

=IFERROR(VLOOKUP(ROWS($M$4:M4),$H$3:$K$20,MATCH(LOOKUP("zz",$B:$B),$H$2:$K$2,0),0),"")

3] Please see attachment



Regards

 

[akankshakhare]

Thank you, but in sample file now when I am selecting main category dropdown to product or files, its not giving dropdown of sub category.

 

[bosco_yip]

Thank you, but in sample file now when I am selecting main category dropdown to product or files, its not giving dropdown of sub category.

In your title and post #.1 you are asking for "Searchable drop down list" and did not mentioned for "sub category drop down list".

And you cannot have either "Searchable drop down list" or "sub category drop down list" in a single cell.

I think your problem is solved

Regards

 

[akankshakhare]

No sir, problm not solved

in post one I mentioned that in column C , I want the dropdown searchable also which is dependent on main category that is column B

 

[Eat More Bacon]

=IF(ISNUMBER(SEARCH(LOOKUP("zz",$C:$C),OFFSET($H3,0,MATCH(LOOKUP("zz",$B:$B),$I$2:$K$2,0)))),MAX($H$2:H2)+1,"")

I'm curious what does the "zz" do in this formula?

 

[new]

is it possible to do this on a dynamic table?

I've been working on a order form that allows you to:
1. select brand from a drop down menu
2. select the products that only appear under that brand in a dependent drop down menu

the order form references a master list (Landed Price List) on another page and the trouble im having is building a searchable function around the xlookup used for the dynamic table.

Data Validation 1: ='Landed Price List (3)'!$O$4#
Data Validation 2 (Dependent): =XLOOKUP($A14,'Landed Price List (3)'!$O$4:$AB$4,'Landed Price List (3)'!$O$5:$AB$5)#

been stuck on this for a while... would love to see if you could find something I missed.

 

[shrivallabha]

is it possible to do this on a dynamic table?

I've been working on a order form that allows you to:
1. select brand from a drop down menu
2. select the products that only appear under that brand in a dependent drop down menu

the order form references a master list (Landed Price List) on another page and the trouble im having is building a searchable function around the xlookup used for the dynamic table.

Data Validation 1: ='Landed Price List (3)'!$O$4#
Data Validation 2 (Dependent): =XLOOKUP($A14,'Landed Price List (3)'!$O$4:$AB$4,'Landed Price List (3)'!$O$5:$AB$5)#

been stuck on this for a while... would love to see if you could find something I missed.

It is preferred to create your own thread if you are posting new requirement. You can provide reference to this thread, if it helps to define your issue.

 

[shrivallabha]

=IF(ISNUMBER(SEARCH(LOOKUP("zz",$C:$C),OFFSET($H3,0,MATCH(LOOKUP("zz",$B:$B),$I$2:$K$2,0)))),MAX($H$2:H2)+1,"")

I'm curious what does the "zz" do in this formula?

LOOKUP function uses binary search and it locates a value which is equal or lower than the LOOKUP criterion. Passing lower case "zz" can catch most of the possible strings. It is one of the most common tricks used inside LOOKUP construct. Another one is BigNum (9.99E+307) for finding last numeric data. See below link:

Excel BigNum | Exceljet

The term "BigNum", which stands for "Big Number", is used to represent the largest allowed positive number in Excel, which is 9.99999999999999E+307 BigNum is used in certain lookup formulas constructed in a way to find the largest value that is less

exceljet.net

.