Cascade Validation

M

matelot

I setup 2 dependent drop-down list using the validation and INDIRECT function
in EXCEL. Everything works fine when I have 2 name ranges that refer to
actual range. 1 cell with validation pointing to NAME1 and the other cell
would have =INDIRECT(cell) in the value box. However, when I try to setup 1
name range as =OFFSET(range,countif(row,condition),height) , it doesn't work
on my spreadsheet. It's giving me an error.
Is it a limitation from EXCEL? Any suggestion?

Thanks
 
B

Biff

Hi!

INDIRECT "expects" a TEXT representation of a reference.

When you use a defined FORMULA to create a dynamic range and then call that
in INDIRECT:

=INDIRECT(MyRange)

MyRange evaluates to a FORMULA, not a TEXT representaion of a reference.

One way to "get around" this is to use CHOOSE instead of INDIRECT. (CHOOSE
can have up to 29 values returned, so you'd need to have <=29 named ranges
to use as the source for the drop downs)

Suppose one drop down contains the named ranges: Rng1, Rng2, Rng3. This drop
down is in cell A1 and the drop down in cell B1 is dependent upon the
selection made from the drop down in cell A1.

Create a list of the named ranges somewhere in the sheet, say, J1:J3:

J1 = Rng1
J2 = Rng2
J3 = Rng3

Now, as the source for the dependent drop down in cell B1:

=CHOOSE(MATCH(A1,J1:J3,0),Rng1,Rng2,Rng3)

Biff
 
Top