Data Validation on formula result

[ad1234]

Am I right in thinking that the above is not possible? I want an error message to appear if the value in one cell (which is the sum of two others) is above a certain value but a search tells me I won't find a way?

 

[BAlGaInTl]

Am I right in thinking that the above is not possible? I want an error message to appear if the value in one cell (which is the sum of two others) is above a certain value but a search tells me I won't find a way?

It depends on how you want the error to appear.

This could be done using conditional formatting along with your formula.

Let say you don't want A+B to be greater than 1

=IF(A+B<=1,A+B,"Error")

Will diplay the value if it is less than or equal to 1 and will display "Error" if it is above 1.

You can then use conditional formatting to change the color of the Error value to make it stand out.

ETA:

If you want a no kidding pop-up message, that could be done using VBA. But I don't think it works with standard data validation.

 

[Jonmo1]

Am I right in thinking that the above is not possible? I want an error message to appear if the value in one cell (which is the sum of two others) is above a certain value but a search tells me I won't find a way?

No, not exactly.
But you can work around it.

YOu put the validation in the Cells that the formula refers to.
A simple example

In C1 you have the formula =A1+B1

You want an error to appear if the value of C1 is above say 100
In A1 and B1, use Data Validation - Custom
=$C1<=100

Hope that helps.

 

[BAlGaInTl]

No, not exactly.
But you can work around it.

YOu put the validation in the Cells that the formula refers to.
A simple example

In C1 you have the formula =A1+B1

You want an error to appear if the value of C1 is above say 100
In A1 and B1, use Data Validation - Custom
=$C1<=100

Hope that helps.

I never thought of doing it that way. Had no idea that worked.

I need to add that to my book of tricks.

 

[Jonmo1]

I never thought of doing it that way. Had no idea that worked.

I need to add that to my book of tricks.

Thanks.