Excel Magic Trick # 259: Dynamic DV List Based On DV List

See how to create a dynamic data validation drop down list based on another dynamic data validation drop down list using excel list / table feature.
Excel 2003 List; Ctrl + L
Excel 2007 Table: Ctrl + T

Also see related video:

YTLE#75: DV Drop-Down based on DV Drop-Down

Category:

Science & Technology

Tags:

• Excel
• Dynamic
• Data
• Validation
• Drop
• Down
• List
• Table
• Mike
• Gel
• Girvin
• Highline
• Community
• College

[Nypsie98]

I have a question about changing the value in the primary DV cell and forcing a change in the secondary DV cell. For example, at the end of your video when you added "hair gel" to the "health" column, you went to the "select product" value and changed to "Health". However, the value in the "Select Item" column stayed as "grains" which is under the "food" list. How can I force the user to change the secondary DV cell when there was a change in the primary? Hope this is clear.

[ExcelIsFun]

@Nypsie98 I am not sure. The problem with data that is already in the cell is that DV does not validate. DV only validates as you enter data...

[Sherlock69Holmes]

Thanks a lot ExcellsFun



[ExcelIsFun]

@Sherlock69Holmes , you are welcome!

[index23432]

Hey Mike thank for your videos it is helping me a lot.

About this lesson....is it possible to create data validation cells using the indirect function in a different worksheet than the tables? i tryed but it didn't work.

anyway thanks.

[ExcelIsFun]

Yes, if you put the table nomenclature names in quotes and then inside the INDIRECT Function, it should work. If the table was names Table1 and the field (column) was named RAD, then this would work in Data Validation List textbox:

=INDIRECT("Table1[Rad]")

[MrPikappordie]

I was able to do it with 5 different 'Products' tables, each table with about 8 'items.' I tried another one with 11 different 'Products,' each with a range of 'items' between 2 and 20, and when I go to data validation I get this message "The source currently evaluates to an error. Do you want to continue?" and I can't track the error. A drop down menu is created, but no items appear. I tried it several times to make sure I was following your procedure and every time I received this message.

[ExcelIsFun]

If a cell has DV and you copy it, the DV will be copied.

[dgodfrey2004]

First, briillant stuff...

Question:  If I had a long list...say 200 items... is there a way to use this indirect function (for the DV List) and copy it down to my last item (#200)? Or do I have to "hard code" each line?

Thanks again!

[ExcelIsFun]

I do not understand your question. Here is a more recent video I made about this trick that has more informtaion:

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

also try:

Excel Magic Trick 548: Data Validation Drop-Down List In A Cell Same Sheet or Different Sheet

Excel Magic Trick 549: Dynamic Data Validation List Drop-Down OFFSET or Table Feature?