Data validation in several levels

  • Thread starter Thread starter CeeFlood
  • Start date Start date
C

CeeFlood

I'd like to have Excel validate data in several layers.

I have 2 columns, 1 for entering Project and 1 for
entering subproject.
The cells in the first column should validate the project
against a list of allowed project. This is no problem, I
use the Validate function.
But, for the second column, I'd like to be able to chose
(in a combo box) from a list of subproject that belongs
only to the project I entered in the first cell.

"Valid list"
Project: Subproject:
A 100
A 110
B 200
B 210

If I chose "A" in the first cell, I'd like to be able to
chose and validate that only "100" or "110" can be used in
the second cell.

Can this be done in Excel without programming?

Thanks
 
say you have a column named range Projects in which you
have a list of individual project names like ,B,C etc
In th enext columns have the sub-projects grouped and
range name them with the project name. so you'd have a
column with 100, 110 range named "A" and the next column
would have 200,210 and be range named B
on your main sheet have a cell with validation set to
List and the source =Projects
in the next cell to the right say B1, set validation to
List and the source set to =INDIRECT(A1)
Each time you change the selection of A1, the available
list in B1 will change


Patrick Molloy
Microsoft Excel MVP
 
Back
Top