Use of Indirect function in Data Validation List

[Cecilkumara Fernando]

Hi All,
This may be a known issue but I came across today.
The named range should be an absolute one for indirect function to work else
it will produce an error.
Name-> Fruits Refers to range=Sheet1!$H$1:$H$20
=Indirect("Fruits") works fine
Name-> Fruits Refers to range=Sheet1!H1:H20
=Indirect("Fruits") raise a #value error
Name-> Fruits Refers to range=OFFSET(Sheet1!$H$2,0,0,20,1)
=Indirect("Fruits") raise a #ref! error
any work around to get a dynamic range as a list for Data Validation???

Regards,
Cecil

 

[Frank Kabel]

Hi
see: http://www.contextures.com/xlNames01.html#Dynamic

 

[Bob Phillips]

Cecil,

The second does not work probably due to being set at a different cell than
used in, so the range will refer elsewhere.

On the third, this works fine for me

=OFFSET(Sheet1!$H$2,,,20,1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Cecilkumara Fernando]

Bob,
Yes, If Named Range "Fruits" Refers to range=OFFSET(Sheet1!$H$2,,,20,1)
and in Data Validation List source: = Fruits it works
but if you put "Fruits" in B2 and try to Validate C2 with
List source: =Indirect(B2) it want.
but If Named Range "Fruits" Refers to range=Sheet1!$H$2:$H$21
and if you put "Fruits" in B2 and try to Validate C2 with
List source: =Indirect(B2) it works.
Is there a way around to have
Named Range "Fruits" Refers to range=OFFSET(Sheet1!$H$2,,,20,1)
and List source: =Indirect("Fruits") get going.
Regards,
Cecil