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" wrote in message
...
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"
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"
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