Several data ranges in data validation

[Babymech]

I have a very simple problem that I just haven't been able to see clearly how
to deal with. In Cell A1 I want the user to be allowed to choose from a list
of project categories, so I've set the Data Validation there to be a list
with members chosen from the named range "ProjectCategories." In cell A2 I
then want the user to be allowed to choose a project, limited to the project
category already selected. So, if the user selects "Internal projects" in A1,
the data validation in A2 should be a list of the members of the named range
"InternalProjects".

The problem for me is setting up the data validation in A2. I can work with
a long IF() statement along the lines of "=IF(A2="Internal
Projects";InternalProjects;IF(...)...)" but that's inelegant, and I have too
many projects in the range ProjectCategories for this to be possible to fit
into the character-limit of data validation. There has to be a better way of
doing that, and I'm betting you know how.

Thanks!

 

[Dave Peterson]

You could build the names nicely.

Debra Dalgleish shows how:
http://contextures.com/xlDataVal02.html

 

[Babymech]

Ah, should have clarified that all my ranges are dynamic and need to be
dynamic. As far as I can tell, this solution doesn't allow dynamic ranges -
just dynamic validation lists, or?

 

[Dave Peterson]

That's what I recall, too.

Ah, should have clarified that all my ranges are dynamic and need to be
dynamic. As far as I can tell, this solution doesn't allow dynamic ranges -
just dynamic validation lists, or?