Data validation - dependent list

hitarov

New Member
Joined
Jul 10, 2013
Messages
6
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.
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

hitarov

New Member
Joined
Jul 10, 2013
Messages
6
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

Board Regular
Joined
Aug 1, 2013
Messages
61
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!!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,121
Messages
5,570,304
Members
412,318
Latest member
angoeyuan
Top