Cascade Validation

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

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

"matelot" <[email protected]> wrote in message
news:[email protected]...
>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

matelot

Have a look at Debra Dalgleish's site for instructions on setting dependent
drop-downs and dynamic ranges.

May be something there that will show what you have missed.

http://www.contextures.on.ca/xlDataVal02.html


Gord Dibben Excel MVP


On Thu, 17 Nov 2005 13:50:06 -0800, "matelot"
<[email protected]> wrote:

>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

Biff,
Thanks for the info. It works like a charm!

"Gord Dibben" wrote:

> matelot
>
> Have a look at Debra Dalgleish's site for instructions on setting dependent
> drop-downs and dynamic ranges.
>
> May be something there that will show what you have missed.
>
> http://www.contextures.on.ca/xlDataVal02.html
>
>
> Gord Dibben Excel MVP
>
>
> On Thu, 17 Nov 2005 13:50:06 -0800, "matelot"
> <[email protected]> wrote:
>
> >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
>
>