Using several list in validation...

**[email protected]** · 07-03-2008, 01:23 PM

Greetings, Gurus,

Consider two text boxes formatted for data validation using list. The first box is for vehicle make, (Ford, Dodge, etc.), and the 2nd is for vehicle model, (Escort, Viper, etc.).

The first box is no problem, it references a list of all vehicle makes. In the 2nd box, I would like to ONLY list models available from the maker selected in the 1st box. My first thought was to simply name a BUNCH of different list, each containing the models for a particular make, and reference them in the validation, something like: (ecat is the name of the list containing ALL models)

Please Login or Register  to view this content.

There were two problems with this concept:
1. There were too many list for me to put them all in as "IF" statements.
2. Although the first couple worked, as I entered more list to choose from I encountered an invalid formula message. (This could be because I didn't have enough ")" at the end of the formula. I'm guessing I need a ")" for every "IF".) Or, maybe I'm supposed to add an "AND" statement.

Is there a way to condense the code, (and make it work), so that I can have approximately 50 validation list to choose from? Is there a better way to go about this?

Thanks in advance for any help you can offer.

Hutch

**NBVC** · 07-03-2008, 01:31 PM

So as long as you've created a named ranges for each make...

Then in validation cell for the models, choose Data|Validation >> List and enter formula =Indirect(A1)

Now the appropriate models will be listed for the make chosen in A1.

**[email protected]** · 07-03-2008, 03:27 PM

Thanks for the reply. I hadn't created named ranges for each MAKE, I only had one named range containing ALL makes, and was referencing that name as the list to validate from. I did create several named ranges for each set of models offered by any make.

I'm not sure I understand your solution. The first box will offer a selection from all vehicls makes, (per my named range), the 2nd box should only offer a selection of models under the selected make.

Could you please clarigy your suggestion? (Sorry, I'm kinda slow.)

Thanks.