data validation error alert does not work

[timp]

I am referencing a list on anther sheet in the same book for data validation
for cells in a range; the in-cell drop downs work fine, the input is user
keyboard, recalc is auto, there are no formulas in the cells and show error
alert is checked. But I get no alert, and if I enter invalid data and then
serch for invald data, there is none. Anyone know why data not in the list
would not be recognized as invalid? (data valdiations elsewhere in the
workbook work fine). Thanks.

 

[Martin Fishlock]

You cannot link to cells on another sheet for data validation except if you
create a named range first.

So on say sheet2 enter you list in cells say a1 apples, a2 pears, a3 oranges.
Then define the range, (insert|name|define) and call it say fruit.
Then in the data validation in the source enter =fruit

Voila, it should work.

 

[timp]

Martin

thanks for the suggestion - the list I am referencing is a named range
(sorry i wasn't clear)

Tim