Use of Indirect function in Data Validation List
 

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

Use of Indirect function in Data Validation List
 

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Cecilkumara Fernando" <cekufdo@sltnetDOTlk schrieb im Newsbeitrag
...
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

Use of Indirect function in Data Validation List
 

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

Use of Indirect function in Data Validation List
 

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

"Bob Phillips" wrote in message
...
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" <cekufdo@sltnetDOTlk wrote in message
...
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