Error using a validated list for named range

[Yeoman.jeremy]

Hi.

I have a named range which expands as more values are entered, formula below:

=OFFSET('Sheet1'!$A$1,0,0,COUNTA('Sheet1'!$A:$A),1)

This previously was a static range, which was not ideal, because it meant every time I added a new product i had to go into the names and increase the row for product, description, cost, price, discounts etc etc etc; so I made it this automatically adjusting range formula.

I also use a Validated list to have a drop down in the cell for this range, which looks like

=INDIRECT(A1)

(the "A1" in this example is the name of the range I want to look up)

So basically, the user chooses from a list of suppliers, which lets them choose (in the next cell) from a list of that suppliers' products.

The Problem:
Now, when I select a supplier (Cell "a1" in this example) it does not give me a list in the next cell, I cannot click on the drop down arrow.
So when I go into the validation settings and save it again it says:
"The Source currently evaluates to an error. Do you wish to continue?"

How can I fix this?

Thanks

 

[wigi]

Hello

This was discussed in an article on **** Kusleika's blog:
http://www.dicks-blog.com/archives/2004/05/11/conditional-data-validation/

 

[Yeoman.jeremy]

Hi,

This does not answer my question.
I already know how to create a validated list using INDIRECT.
I explained this in my question

Thanks