countif in Data Validation stops working?

[wal]

Excel 2007

I have the following formula in the cells of column A to warn me of
duplicate data:

=COUNTIF($A$2:BOTTOM_A,A561)<=1

(BOTTOM_A is the name of a cell at the bottom of the the part of
column A I'm working in.)

For some reason, the alert I set up no longer fires when I key in a
duplicate entry. However, the exact same setup in column C still
works.

Also, I've set up a "backup" warning in column A using colored
conditional formatting (with the same basic formula), and that does
work. If I make the range smaller (BOTTOM_A is at A1500), the alert
works again.

Is it simply because there's too much data in column A? (But that
doesn't prevent the alert in Column C from working.)

This is a kind of open-ended question, but if anyone has any ideas as
to why the validation no longer works, I'd appreciate them. Thanks.

 

[T. Valko]

Just a guess...

In the validation setup wizard uncheck "Ignore blank".

 

[wal]

Your suggestion worked! Thanks. Although I still know understand why
the analogous formula-based validation in column C, with the "Ignore
blank" box still checked, works as is.

While we're on the subject, what does the following paragraph from the
Help file mean in English? I think the second sentence applies to my
situation.

*****If your allowed values are based on a cell range with a defined
name, and there is a blank cell anywhere in the range, setting the
Ignore blank check box allows any values to be entered in the
validated cell. This is also true for any cells referenced by
validation formulas: if any referenced cell is blank, setting the
Ignore blank check box allows any values to be entered in the
validated cell.****

The second sentence sounds like it contradicts the basic meaning of
validation. If the range you're checking against contains blank cells
(as in my case), if you fill in the "Ignore blank" checkbox, any value
entered into the current cell will be accepted -- even if it violates
your custom formula?? What's the point of this extra setting? (And
the label "ignore blank" doesn't really have the meaning of what it
effects.)

 

[T. Valko]

I find the help files not too helpful in most cases. My guess is that the
help files were written by *programmers* from the standpoint of a programmer
and not an end user. The result is a general lack of clarity! IMHO

With that said, here's what I know...

At one time I too was confounded by this behavior. So, I posted a similar
question in another forum.

The problem is caused by using a defined name in the validation formula and
the "fix" is to uncheck "Ignore blank" but as far as *why* this happens,
which is what I like to know, nobody knows why or has an explanation.

It's just one of those quirks about Excel that you remember once you've
encountered it.

--
Biff
Microsoft Excel MVP


Your suggestion worked! Thanks. Although I still know understand why
the analogous formula-based validation in column C, with the "Ignore
blank" box still checked, works as is.

While we're on the subject, what does the following paragraph from the
Help file mean in English? I think the second sentence applies to my
situation.

*****If your allowed values are based on a cell range with a defined
name, and there is a blank cell anywhere in the range, setting the
Ignore blank check box allows any values to be entered in the
validated cell. This is also true for any cells referenced by
validation formulas: if any referenced cell is blank, setting the
Ignore blank check box allows any values to be entered in the
validated cell.****

The second sentence sounds like it contradicts the basic meaning of
validation. If the range you're checking against contains blank cells
(as in my case), if you fill in the "Ignore blank" checkbox, any value
entered into the current cell will be accepted -- even if it violates
your custom formula?? What's the point of this extra setting? (And
the label "ignore blank" doesn't really have the meaning of what it
effects.)