I believe the named range should work (and did in my quick test).
As for the first approach, you should change the table range to use an
absolute reference; instead of A1001:A1100, user $A$1001:$A$1100.
The point of an absolute reference is exactly what you're looking for.... it
doesn't change as it gets copied down or across.

"EVO" wrote:

I am trying to validate an entry based upon it NOT being on a list. (The
entry is a part number and the list is a table of currently out-of-stock
items, so I want to present an appropriate warning message).

Here is the validation custom formula:
=IF(ISERROR(VLOOKUP(Q2,A1001:A1100,1,FALSE)),TRUE, FALSE)

This works fine, but when the cell is copied down the column, the value Q2
(the input cell itself) changes as we would expect and hope. However, so does
the lookup range A1001:A1100 which is a disaster.

I tried giving the Out of Stock table a name and using that in place of the
absolute range, but that just does not work. It does not appear that the
custom validation formula can accept a data name. I would prefer this
solutions so that I can put the Out of Stock table in another tab. This is
what I would like:
=IF(ISERROR(VLOOKUP(Q2,OutOfStock,1,FALSE)),TRUE,F ALSE)

Anyone have an idea?
--