Excel: Conditional Data Validation List

[AJD10]

I have data validation for a drop down list with a condition with the named ranges to populate the dropdown:
IF(A2 = "1", List1, IF(A2="2", List2, ListAll))

This was great while we had limited number of lists. Now I have 10 lists and there is a limit in the Data Validation source box.

What is the best way for someone to choose an option in Column A and have Column B produce the corresponding list? I was trying to do a VLookup, but can't do it with data validation.

I want to make subtle changes and prefer not to add combo boxes, etc.

Any suggestions?

 

[SkipVought]

Hi,

Use the INDIRECT function
[tt]
=INDIRECT("List"&A1)
[/tt]
The Concatenation result must be the name of one of your lists.

Skip,
[sub]
[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it![/sub]

 

[AJD10]

Oh great! this worked - I never tried the Indirect feature before.

Thanks so much!!

 

[jmhicsupt]

I am trying to do the same thing here -- "I have data validation for a drop down list with a condition with the named ranges to populate the dropdown".

The response said to use INDIRECT formula. I don't understand how to do this.

Can you give me the formula of how it would be written?

Thanks in advance.

 

[jmhicsupt]

Duh on me. I figured it out based on the above. Thanks - this site is so helpful!