Offset Named range in Datavalidation List
 

Hi All,

I'm able to refer a sheet1 named range for a datavalidation list box in
sheet2.
Data-Validation-Allow=List,Source="=products" (products is named range in
sheet1)

Later defined a named range
products_offset="=OFFSET($A$1,0,0,CountA($A:$A),0) "

Then I tried to update the list source (Data Validation) in sheet2 i.e.,
updated 'products' with 'products_offset'. After pressing Enter key I'm
getting an error as "The source currently evaluates to an error. Do you wish
to continue?" By clicking on 'Yes' I'm not able to see any item in sheet2
list box.

I've also tried with source as =INDIRECT("products_offset"), but still
getting above error.

Here my question:
How can I refer named range with offset foramulae as source for list box
(Data validation) in another sheet.

Can any one share your thoughts and ideas on over coming the issue discussed
at 'Here my question:'?

Your help/support will be appreciated.

Advanced Thanks,
Vinod

Offset Named range in Datavalidation List
 

Later defined a named range
products_offset="=OFFSET($A$1,0,0,CountA($A:$A),0 )"

You have the width argument set to 0. Either leave it out or set it to 1:

=OFFSET($A$1,0,0,CountA($A:$A))

=OFFSET($A$1,0,0,CountA($A:$A),1)

--
Biff
Microsoft Excel MVP


"Vinod" wrote in message
...
Hi All,

I'm able to refer a sheet1 named range for a datavalidation list box in
sheet2.
Data-Validation-Allow=List,Source="=products" (products is named range
in
sheet1)

Later defined a named range
products_offset="=OFFSET($A$1,0,0,CountA($A:$A),0) "

Then I tried to update the list source (Data Validation) in sheet2 i.e.,
updated 'products' with 'products_offset'. After pressing Enter key I'm
getting an error as "The source currently evaluates to an error. Do you
wish
to continue?" By clicking on 'Yes' I'm not able to see any item in sheet2
list box.

I've also tried with source as =INDIRECT("products_offset"), but still
getting above error.

Here my question:
How can I refer named range with offset foramulae as source for list box
(Data validation) in another sheet.

Can any one share your thoughts and ideas on over coming the issue
discussed
at 'Here my question:'?

Your help/support will be appreciated.

Advanced Thanks,
Vinod

Offset Named range in Datavalidation List
 

Thanks T.Valko, for your immediate response.

Your solution is absolutely working for me without issues. Your immediate
attention and support is highly appreciated.

Once again Thanks for correcting the mistake and puting me in right direction.

Regards
Vinod

"T. Valko" wrote:

Later defined a named range
products_offset="=OFFSET($A$1,0,0,CountA($A:$A),0 )"

You have the width argument set to 0. Either leave it out or set it to 1:

=OFFSET($A$1,0,0,CountA($A:$A))

=OFFSET($A$1,0,0,CountA($A:$A),1)

--
Biff
Microsoft Excel MVP


"Vinod" wrote in message
...
Hi All,

I'm able to refer a sheet1 named range for a datavalidation list box in
sheet2.
Data-Validation-Allow=List,Source="=products" (products is named range
in
sheet1)

Later defined a named range
products_offset="=OFFSET($A$1,0,0,CountA($A:$A),0) "

Then I tried to update the list source (Data Validation) in sheet2 i.e.,
updated 'products' with 'products_offset'. After pressing Enter key I'm
getting an error as "The source currently evaluates to an error. Do you
wish
to continue?" By clicking on 'Yes' I'm not able to see any item in sheet2
list box.

I've also tried with source as =INDIRECT("products_offset"), but still
getting above error.

Here my question:
How can I refer named range with offset foramulae as source for list box
(Data validation) in another sheet.

Can any one share your thoughts and ideas on over coming the issue
discussed
at 'Here my question:'?

Your help/support will be appreciated.

Advanced Thanks,
Vinod

Offset Named range in Datavalidation List
 

You're quite welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Vinod" wrote in message
...
Thanks T.Valko, for your immediate response.

Your solution is absolutely working for me without issues. Your immediate
attention and support is highly appreciated.

Once again Thanks for correcting the mistake and puting me in right
direction.

Regards
Vinod

"T. Valko" wrote:

Later defined a named range
products_offset="=OFFSET($A$1,0,0,CountA($A:$A),0 )"

You have the width argument set to 0. Either leave it out or set it to 1:

=OFFSET($A$1,0,0,CountA($A:$A))

=OFFSET($A$1,0,0,CountA($A:$A),1)

--
Biff
Microsoft Excel MVP


"Vinod" wrote in message
...
Hi All,

I'm able to refer a sheet1 named range for a datavalidation list box in
sheet2.
Data-Validation-Allow=List,Source="=products" (products is named
range
in
sheet1)

Later defined a named range
products_offset="=OFFSET($A$1,0,0,CountA($A:$A),0) "

Then I tried to update the list source (Data Validation) in sheet2
i.e.,
updated 'products' with 'products_offset'. After pressing Enter key I'm
getting an error as "The source currently evaluates to an error. Do you
wish
to continue?" By clicking on 'Yes' I'm not able to see any item in
sheet2
list box.

I've also tried with source as =INDIRECT("products_offset"), but still
getting above error.

Here my question:
How can I refer named range with offset foramulae as source for list
box
(Data validation) in another sheet.

Can any one share your thoughts and ideas on over coming the issue
discussed
at 'Here my question:'?

Your help/support will be appreciated.

Advanced Thanks,
Vinod