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!

 

[TheDitto]

Indirect+offset

Hi, I just thought I'd follow up with this thread as I am experiencing the identical problem.

Here's the situation:

Fresh sheet:

A1=a
A2=b
A3=c

B1=d
B2=e
B3=f

Define named range: "List1" as =Sheet1!$A$1:$A$3
Define named range: "List2" as =OFFSET(Sheet1!$B$1, 0, 0, 3, 1)

List2 is using Offset, but simplified to reduce other issues from creeping in. My original problem has a much more complex OFFSET function, but this example seems to demonstrate the error perfectly.

C5=List1
C6=List2
(both strings .. )

D5 - Setup Data Validation: List -> Source =INDIRECT(C5)
Click OK

It takes it ..
Use the drop down, you can see "a, b or c" correctly ..

D6 - Setup Data Validation: List -> Source =INDIRECT(C6)
Click OK .. get an error: "The Source currently evaluates to an error. Do you wish to continue?"

Proof that the "source" does not evaluate to an error:
A6=match("e", List2, 0)
Evaluates correctly to "2" ..

It seems that INDIRECT and OFFSET are incompatible? How to get them working? what are the options for using INDIRECT with a NAMED RANGE which uses OFFSET. (ie is there another way to get THERE from HERE?)