Excel 2007 - Data Validation Quandry

[Scott24x7]

All,
I'm using Excel 2007, and I've used a lot of Data Validation to provide drop down lists for my users. However, I have encountered an issue where I have a drop down in the column before, that I want to provide differnt options in the dropdown in the next column, based on what was selected in the previous column... However, when Clicking Data Validation from the ribbon bar, it doesn't seem to have any ability to deal with conditional circumstnaces.
My question is, is there a way to provide a more "dynamic" drop-down in Excel (preferably in sheet, as I like to avoid Macro, as many of my users have Macro disabled, and I won't be able to get them to move off that...)
I have created on a seperate page some named ranges that I use to populate my drop-down list. This works fine... Just don't have a way to say use "This List" or "That List".
Any options?


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[SkipVought]

Hi,

Please be more specific with your requirement. Post an example of the list data you are dealing with.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Scott24x7]

Skip,
The list itself isn't the issue, but basiclly, imagine this:

CEll B2 has 2 possibilities (and is a drop down, from Data Validation) The options are X or Y.
CELL C2, I want to have as a drop down as well. But if you pick X, I want the itmes 1,2,3 to be presented, and if you pick Y I want the options 4,5,6 to be presented.
I simply mentioned I'm using named ranges to fill the contents of the list, but it doesn't have to be, if there is another option that works.
Thanks

Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[PeterMoran]

Hi,

You may like to checkout this url:

http://www.contextures.com/xlDataVal01.html

I think you will find it a most useful sight for Data Validation.

Good Luck!

Peter Moran

 

[GlennUK]

I must also recommend Debra Dalgleish's site Contextures for Data Validation ( and PivotTable tricks ... fantastic ).

The way you are likely to go is to use List of =INDIRECT(first_choice_cell_reference) for the second list Data Validation.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Fenrirshowl]

Scott

If I understand correctly, what you need is to set up a list of inputs for the second and subsequent boxes depending on the previous validate cell entries?

What I generally do is create the subsequent lists somewhere on the worksheet and name them. The names are the values in the previous validation.

If your first validated list is alpha, beta, gamma and is in cell A1,
and you have defined second lists of entries in named ranges called alpha (=X1:X4), beta (=Y1:Y4), gamma (=Z1:Z4) then you can use the INDIRECT function within the cell validation to tell it which input to use.

So if your second validated cell is B1, you can validate it with =INDIRECT(A1), [when you define the Allow type in the validation as a list]

I hope this makes sense.

There is one issue though - if you make changes to all your cells and then change the first validated cell, the subsequent cells do not clear, so you may be left with junk results.

Say you select beta in A1, you can select an entry from the validated list in B1, then change A1 to alpha, and you still retain the original value un B1.
This value may no longer be valid as it may not for part of list alpha.
You will therefore need some sort of errorhandling for this such as a check cell to the side of the validation to confirm the entry in cell B1 is an entry in the appropriate range or prompt the user to reselect an entry in a subsequent validated cell.


i.e. in C1 =IF(ISERROR(MATCH(B1,INDIRECT(A1),0)),"Invalid entry","")

Hope this helps.

Fen

 

[SkipVought]

I answered a similar question recently in thread68-1541507.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}