I am using a formula to define a range name, that range name will then be
used to populate a data validation drop-down list. The formula for the range
name is

=OFFSET(Sheet2!$A$2,,0,COUNTA(Sheet2!$A:$A),1)

I have set up a range name called 'mylist' using this formula. I now want
to be able to set up the data validation on a cell so that if 'mylist' is
typed in cell A1 the cell in B1 displays the 'mylist' validation list. This
works using the INDIRECT function if I define my range name manually as just
a simple range of cells but fails when the range name is defined by a
formula. Does anyone have any ideas on how I can get the data validation
settings to pick up the range name correctly from the text entered in a cell?

A Jones

Hi, Andrea -

It sounds like you need a dependent dropdown list. Check out this link to
one of Debra Dalgleish"s pages:

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

Be sure to alphabetize both lists.

Carole O
"Andrea Jones" wrote:

I am using a formula to define a range name, that range name will then be
used to populate a data validation drop-down list. The formula for the range
name is

=OFFSET(Sheet2!$A$2,,0,COUNTA(Sheet2!$A:$A),1)

I have set up a range name called 'mylist' using this formula. I now want
to be able to set up the data validation on a cell so that if 'mylist' is
typed in cell A1 the cell in B1 displays the 'mylist' validation list. This
works using the INDIRECT function if I define my range name manually as just
a simple range of cells but fails when the range name is defined by a
formula. Does anyone have any ideas on how I can get the data validation
settings to pick up the range name correctly from the text entered in a cell?

A Jones