If statement based data validation? Dynamic data validation?

**bjohnsonac** · 10-02-2014, 01:11 PM

I am working on a file that has three groups of 10 numbers each and three groups of 10 descriptions to go along with each of those numbers. I am planning on making a drop down validation (using a named range) for cell "B15" to contain the 30 numbers. Ideally, depending on which number they picked a specific validation drop down group of descriptions would then populate cell "D15". Group 1 of the numbers all start with "112##", group 2 starts with "113##" and group 3 starts with "114##" with the "#" being variable. If the user selects a number that starts with "112##", I need a specific 10 item validation drop down list to then populate in cell D15. If the user selects a number that starts with "113##" a different set of 10 item validation would populate, and so on for selecting numbers that start with "114##".

Truncated Example:

Group 1 #'s_________________Group 1 Descrips
11200_______________________PL Modeling
11210______________________ Shop Drawings
11220 ______________________PL Risers
11230______________________ PL Pre-Fab

Group 2 #'s________________Group 2 Descrips
11300______________________PP Modeling
11310______________________PP Shop Drawings
11320______________________PP Risers
11330______________________PP Pre-Fab

Group 3 #'s______________________Group 3 Descrips
11400______________________SM Modeling
11410______________________SM Shop Drawings
11420______________________SM Risers
11430______________________SM Pre-Fab

User selects 11220 in cell B15, Cell D15 then offers a drop down including the "PL" description options

Been working on this for two full days now and am totally confused

Any help would be appreciated!

**hoyasaxa215** · 10-02-2014, 01:59 PM

Sounds like you need a dependent dropdown list. See link below.

http://www.contextures.com/xlDataVal02.html

**bjohnsonac** · 10-02-2014, 02:12 PM

Thank you for your reply. I already know how to do traditional dependent drop downs, its the complexity of this situation that is keeping me from being able to create them.. I was hoping not to have to create 30 different INDIRECT data validations. Since it is basically "If any of the numbers from group 1 of 10 numbers is selected, then give a list of these 10 descriptions....OR, if any of the numbers from group 2 of 10 numbers, then give list of 2nd 10 descriptions, etc. Using the INDIRECT route for validation is very cumbersome for this application.

Maybe I have been looking at it for too long and am over-complicating it