Drop Down List based on Cell Value

[nburaq]

Hi,
I have two sheets, at first sheet I have cell values for the drop down list and 2nd sheet inlcudes drop down list. However, I could not manage to solve my problem via data validation. Could you help me to solve this problem?

Sheet1:
Column A Column B(drop down list based on value column A)

169-00
213-00
011-00
056-00

Sheet 2: (PS: I want to list all values starting with 169 at drop down list)

169-00	LIFELINE
169-01	TRUCK UNLOADER
169-02	MACHINE
213-00	PACKING

Thanks in advance for your help!

 

[James006]

Hi,
You request is pretty much Standard .... dependent drop-down lists
e.g. see : Dependent Drop-Down Lists

Hope this will help

 

[nburaq]

Hi James,
Thanks for your reply and help. However, I have checked this before but this is not the solution for my problem because data validation list only allows you to list one column if you try to choose to columns to be listed in data validation it gives "The list source must be a delimited list or a reference to single row or column" error. I don't know maybe solution is VBA?

 

[James006]

Hi again,

Sorry I did not understand you wanted to created a multi-column data validation list ...

For that matter, you could Insert a Form ComboBox to have the flexibility of the number of columns ...

But you have a plan to go further into coding ... then you should, indeed, use an ActiveX Combobox and use VBA

Hope this clarifies

 

[nburaq]

I tried to solve my problem using a helper column using in column E at sheet 2 for concatenated result and column H2 for filtered result to be used for validation list.

=FILTER($E$2:$E$100;--ISNUMBER(SEARCH(LEFT(Sheet1!$A2;3);$E$2:$E$100));"Not Found")

But this time, my dropdown list everytime shows whatever the value of cell A2 IN Sheet1 (in this case it is 169-00) so in sheet1 dropdown list should change with cell reference. If A2 is 169-02 then I should be able to see the filtered results at dropdown list.

I hope you guys can solve this problem. Thanks again

 

[nburaq]

Hi Again,
I am still struggling with a proper solution. Just to give an idea I attached a screenshot. In screenshot, if N2 is 060-00 then it will list possible matches at data validation list based on what cell value N is. The list to be used on data validation list is in sheet 2

Thanks again and I hope this time I will find a solution with the help of you guys!