Conditional validation :-)

[Guest]

Hi,

I am using list validation in column A where user can choose 1-9 in each
cell. What I wish to create is:

if A1=1 then the validation list for B1 is 1.1 to 1.9
if A1=2 then the validation list for B1 is 2.1 to 2.9

and so on.... In effect, its conditional which validation list is being used.

How do I set this up??

Thanks in advance
Robert

 

[Biff]

Hi!

Here's one way:

Create a table like this somewhere on your sheet:

1 1.1 2.1 3.1 ... 9.1
2 1.2 2.2 3.2 ... 9.2
3 1.3 2.3 3.3 ... 9.3
4 1.4 2.4 3.4 ... 9.4
...
9 1.9 2.9 3.9 ... 9.9

Assume the table is in the range A2:I10.

Create a defined name for A2:A10 like Numbers.

Create defined names for each of the other ranges as they
correspond to the numbers list like One, Two, Three, Four,
etc.

For the source of your dropdown in A1 use: =Numbers

For the source as the dropdown in B1 use:

=CHOOSE(A1,One,Two,Three,Four,etc...,Nine)

See this site for more info:

http://contextures.com/xlDataVal02.html

Biff