Data validation - dependent list

[hitarov]

Dear all,

Please help me with below case:

B2 should be drop-down list based on the value in B1.
Example: If Function code is 901, the drop down list should contains 3 values: Warehouse, Transport, QC only.

As we have ~100 function codes, formular with IF that I tried -> Data validation IF(B1="901",901,IF(B1="902",902 …. are not applicable.

I tried Excel Data Validation -- Dependent Lists but it didn't works.

Thank you very much.

 

[TrumpExcel]

I have done this using named ranges and indirect formula

check if this helps - Creating a Dependent Validation Drop-down List

 

[hitarov]

I have done this using named ranges and indirect formula

check if this helps - Creating a Dependent Validation Drop-down List

Hi TrumpExcel,

Thank you very much.
It works, just Ctrl + Shift + F3 didn't work for my Excel version (2010), is it your customized short cut?
However, with this solution we have to create hundreds of name range, just wonder if there's a better solution.

Thank you a lot for you reply.

 

[TrumpExcel]

Hi TrumpExcel,

Thank you very much.
It works, just Ctrl + Shift + F3 didn't work for my Excel version (2010), is it your customized short cut?
However, with this solution we have to create hundreds of name range, just wonder if there's a better solution.

Thank you a lot for you reply.

Hi Hitarov,

Control + Shift + F3 is a default shortcut. If that is not working, you can go to Formulas-->Defined Names--> Create from selection.

Also, I could come up with the following way. First create a drop down list for Function Code.

Then use the following formula in data validation List for Function Name
=OFFSET($B$4,MATCH($B$1,$B$4:$B$17,0)-1,1,COUNTIF($B$4:$B$17,$B$1))

Hope this works fine!!