Offset Named range in Datavalidation List

[Vinod]

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

 

[T. Valko]

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)

 

[Vinod]

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]

You're quite welcome. Thanks for the feedback!