SOLVED

Data Validation Error Alerts for Cells with Formulas

Brass Contributor

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 Replies
best response confirmed by warrevar (Brass Contributor)
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:

Martin_Weiss_0-1688568782694.png

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!","")

Martin_Weiss_1-1688568912732.png

 

Kind regards,

Martin

@Martin_Weiss bummer!

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

Accepted Solutions
best response confirmed by warrevar (Brass Contributor)
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:

Martin_Weiss_0-1688568782694.png

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!","")

Martin_Weiss_1-1688568912732.png

 

Kind regards,

Martin

View solution in original post