Several data ranges in data validation
 

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!

Several data ranges in data validation
 

You could build the names nicely.

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

Babymech wrote:

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

Several data ranges in data validation
 

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" wrote:

You could build the names nicely.

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

Babymech wrote:

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

Several data ranges in data validation
 

That's what I recall, too.


Babymech wrote:

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" wrote:

You could build the names nicely.

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

Babymech wrote:

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


--

Dave Peterson