Data Validation Error Alerts for Cells with Formulas

[warrevar]

I'm adding an Error Alert to a spreadsheet through Data Validation.  I should receive an error message whenever:

BP3*1>$BP$4*1

If I manually enter a number into cell BP3 that is greater than BP4, then I get the appropriate error message.  However, cell BP3 is supposed to be a formula; and when I enter a formula with a calculated value exceeding BP4, it does NOT give the appropriate error message.

I thought I could just multiply both values by 1, but this doesn't help.  Any ideas?

[#2 · Accepted solution]

Hi @warrevar 

no, data validation rules apply only for manual entries, not for formulas.

You could instead use conditional formatting, for example to highlight the cell if the calculated value breaks the rule. Example:

Or you could enter a message in a cell beside BP3, that only appears if the rules apply:

=IF(BP3>BP4,"Warning: Value is to high!","")

Kind regards,

Martin

[#3]

@Martin_Weiss bummer!

Back to the drawing boards then. Thanks for your help!