Validate Entry Custome Formula and Data Names

[EVO]

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,FALSE)

Anyone have an idea?
--

 

[bpeltzer]

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]

Thanks so much... the absolute address works fine. I should have realized
that. I tested the name again and it just won't work. Works fine in a cell.
Copy the exact formula to the Validate custom entry and it won't work. OK,
thanks again.

 

[T. Valko]

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

 

[EVO]

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]

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?

 

[EVO]

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]

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.

 

[EVO]

Sure. Let's look at it. I also hate it when a work-around is necessary
because a "should have worked" solution is not working. But, usually when I
dig into one of these, it ends up being operator error.

EVO

 

[T. Valko]

OK, you can send the file to me at:

xl can help at comcast dot net

Remove "can" and change the obvious.

Include a detailed explanation of where everything is and what you tried
that didn't work. If the file is big zip it. My email has problems with
attachments >1mb.

 

[artanbori]

does this work?

if you validate data in cell with a custom sumif formula "=
sumif(range,"text",sumrange)"
plus a sum(sumrange)
Then if you check the “Circle Invalid Data" button
The trouble is you have to manually check the"Circle Invalid Data" every
time you save the file or refresh the circles vanish, for some reason it does
not automatically appear when data becomes invalid - I don't know why
And the circles disappear again when you refresh or save the file so guess
it is necessary/useful to check sheet every now and then by clicking on the
"Circle Invalid Data" button

I do not know why they don't automatically appear when set but you cannot
adjust the sum or create a new sum in the cell if the required data/text is
missing
Maybe the same reason the fill series does not work for weekdays even
thought the option is so tantalisingly placed for our choice.