Excel Magic Trick 550: Data Validation List Drop-Down Based On 2nd Data Validation List Drop-Down

See how to create a Drop Down List that is based on another drop down list using there features:
1)Data Validation List
2)Defined Names
3)Create Names From Selection
4)INDIRECT function
5)SUBSTITUTE function
Drop down based on Drop down list
Data Validation based on another data validation
Dependent Data Validation

Category:

Science & Technology

Tags:

• Excel
• 2007
• 2010
• Data
• Validation
• List
• Drop-Down
• Based
• On
• 2nd
• excelisfun
• Mike
• Gel
• Girvin
• Highline
• Community
• College
• Dependent
• INDIRECT
• SUBSTITUTE
• function

[jboyd3737]

This is awesome! I'm working with data that has a "/" in it and spaces. Is there a way to use "if" function so that the formula will look at the string and replace either the "/" or the space " " with the "_" underscore?

[ExcelIsFun]

@jboyd3737 , use SUBSTITUTE Function.

[iceoniceone]

u'r a genius!!! the fastest and most efficient explanation!!

[ExcelIsFun]

@iceoniceone , no genius, just a guy having fun with Excel!

[SleurKlub]

Can you make this work if A9:A14 are a range of numbers? for instance:

A9 = 1-10; A10 = 11-20, etc.

Many thanks!

[ExcelIsFun]

I do not know how to do that. Try posting to THE best Excel question site:

mrexcel[dot]com/forum

[doosara2000]

@ExcelIsFun

U r a real genius...... i got good name in my office bcoz of ur excel videos...

Thank you a lot.... :)

i need ur help again , can u please explain me how to create a list from tow coulmns( eg : column A contains the header of the List, column B contains the values of the list ) ...

if solved before plz give me the video excel no.

[alsalms3]

@ExcelIsFun Hello All, I have watched this video through Youtube..... Excel Magic Trick 550: Data Validation List Drop-Down Based On 2nd Data Validation List Drop-Down My Question, I need to create another dependent drop down list based on the first one? If i change the first list "A" so i will get the results on "B" and "C" i

[ek196]

Hi, how do you add the 2nd DV drop-list when the cell is beneath the 1st drop list? the indirect function works well if the 2nd listis to the right of the 1st list.

[tidydee84]

@74ddsmith I'm getting the same message

[74ddsmith]

I have tried to recreate your spreadsheet and am following along, but when I add indirect I and click OK I get "The Source currently evaluates to an error. Do you want to continue? I am guessing this is a user error, what am I doing wrong?