If the validation doesn't work that means that for some reason the formula
is either returning an error or the result of the formula is FALSE provided
the validation was properly applied.

I know for certain that using a named range works but I tested it anyhow and
it did work as I expected.

Using the specific range instead of a named range is no big deal however, I
don't "like it" when something that *should work* doesn't and I like to find
out why! So, if you want to get to the bottom of this I'd be glad to look at
your file and figure it out.

If you want to do that let me know and I'll let you know how to contact me.

--
Biff
Microsoft Excel MVP


"EVO" wrote in message
...
No error message. The validation doesn't work. (If the name is in the list
it
doesn't give the error alert).

The name is defined just the way any one would be. In this case it is
"=Contacts!$A$1001:$A$1100".

If I do the test in a cell, it works. For instance
"=COUNTIF(OutOfStock,Q2)=0" - your formula - returns TRUE of FALSE
depending
on the value of Q2, but it doesn't in the Validation formula of Q2. Yet,
"$A$1001:$A1100" does work.

Go figure.

Not sure what a dynamic range is.


"T. Valko" wrote:

Exactly what happens when you say it's not working with the named range?

Do you get some kind of error message? Is the named range static or is it
dynamic?

--
Biff
Microsoft Excel MVP


"EVO" wrote in message
...
Biff - the Countif is truly clever. A great way to see if something is
in
a
table. Thanks. I'll use it next time. I have already propagated the
vlookup
into 2,400 cells! I am totally stumped as to why the name is not
working.
I
did a small test on a blank workbook and it works fine. Just won't work
in
this one.


"T. Valko" wrote:

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

You can reduce that to:

=ISNA(VLOOKUP(Q2,A1001:A1100,1,0))

The named range should work:

=ISNA(VLOOKUP(Q2,OutOfStock,1,0))

But, you can reduce that even further to:

=COUNTIF(OutOfStock,Q2)=0


--
Biff
Microsoft Excel MVP


"EVO" wrote in message
...
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?
--