Data Validation bug in Excel

[JohnWmBeckner]

I am using Excel 2007. I want to validate data in a cell based on a range but also with the ability to override based on another cell. Specifically, cell F55 is my data entry formatted as a percentage. Cell AA55 is my minimum value, AB55 is my maximum value. If I place "Y" in cell P72, then the range is ignored and any value is acceptable. Normally P72 is blank.

My conditional test under custom data validation is:

=IF(UPPER(P72)="Y", TRUE, AND(F55>=AA55, F55<=AB55))

This works fine if P72 is not blank. If P72 is blank, then validation does not occur. I copied the validation formula into an empty worksheet cell and if I have P72 blank and enter an invalid value into F55, the formula shows "FALSE" which is what I would expect, however the data validation does not return an exception as it should. If I enter "N" (or any text other than "Y") in P72, the function works.

Does anyone know why this formula does not work in data validation but works as a cell value?

Thanks for any help I can get - this has been extremely frustrating!

John Wm Beckner
JohnWmBeckner@hotmail.com.nospam

[starguy]

I am using Excel 2007. I want to validate data in a cell based on a range but also with the ability to override based on another cell. Specifically, cell F55 is my data entry formatted as a percentage. Cell AA55 is my minimum value, AB55 is my maximum value. If I place "Y" in cell P72, then the range is ignored and any value is acceptable. Normally P72 is blank.

My conditional test under custom data validation is:

=IF(UPPER(P72)="Y", TRUE, AND(F55>=AA55, F55<=AB55))

This works fine if P72 is not blank. If P72 is blank, then validation does not occur. I copied the validation formula into an empty worksheet cell and if I have P72 blank and enter an invalid value into F55, the formula shows "FALSE" which is what I would expect, however the data validation does not return an exception as it should. If I enter "N" (or any text other than "Y") in P72, the function works.

Does anyone know why this formula does not work in data validation but works as a cell value?

Thanks for any help I can get - this has been extremely frustrating!

John Wm Beckner
JohnWmBeckner@hotmail.com.nospam

try this

=OR(AND(P72="",F55>=AA55,F55<=AB55),UPPER(P72)="Y")

[JohnWmBeckner]

StarGuy, thanks but the result is the same. I have tried many variants of my formula with no success.

John

[starguy]

StarGuy, thanks but the result is the same. I have tried many variants of my formula with no success.

John

can you attach a sample file?

[JohnWmBeckner]

The workbook contains trade secrets and I am under an NDA. So I stripped everything but what is pertinent.

Thanks,

John