I have two cells A1 and A2.

Cell A1 is a formula that should return a result of zero. Cell A2 is a variable input cell and currently populates the number 2.

I wish to set up a data validation warning that alerts me that the result in A1 is outside +/- the value in A2. So if it returns +1, + 2 or -1, -2 = no warning. Any result above 2 or below 2 then gives me a warning. The data validation needs to apply to the cell A1 result and is linked to cell A2 (as a variable, i.e change 2 to another tolerance number).

I have tried all the possible Data Validation permutations, but nothing works for me. But very likely I am missing something that is staring me in the face.

All help greatly appreciated. Thank you.

### Replies (12)

### Please login or register to join the discussion.

Any result above 2 or below 2 then gives me a warning.

Did you mean

*Any result above 2 or below -2 then gives me a warning*?

If so, try

=IF(OR(A1>B1,A1<-B1),"ALERT","")

Thanks for responding.

Yes, I would like the warning message to pop up if the result is > 2 or < -2 but as a Data Validation function message.

I already have that IF statement there as a secondary flag up. :)

Cheers.

No worries, I didn't pick up on *Data Validation* - too recent for my Excel.

You could try amending the 2 in your question to -2 (via the Edit function), which might encourage other responses.

How about using conditional formatting, testing on VALUE(ABS(A1))>2 to return RED highlighting?

Thank you Lambert Clerical. I already have such a red highlight and a suitable message in the formula. But the solution I seek is one that uses the Data Validation function so it doesn't matter where in the (large) workbook I am, if the error occurs the message will pop up on the screen and immediately warn me. A cell formula solution will only be of use if you are actually viewing that formula cell at the time of the error.

I hope my challenge makes sense.

I am not sure I completely understand your requirement. I am assuming that, wherever input is being made in the spreadsheet, this input should not allow the variance figure in A1 to exceed the tolerance level in A2.

The following Data validation could be applied to every potential input cell:

Assign a range name "Variance" to A1 and "Tolerance" to A2. The data validation custom formula is "=ABS(Variance)<=Tolerance" and set the Error Alert tab to "Warning" with a heading and message of your choice.

Does that help?

I feel the suggested conditional formatting solution may be better as it remains visible even after input. However, how about a custom formula in the data validation box of =ABS(A1)<=A2 with the error alert tab set to Warning and whatever message you wish displayed?

Hi Paul, thank you for your perseverance.

I have tried both of your suggestions, but neither is producing the Data Validation message pop-up. Also, should it be ">=", rather than "<=" which I have also tried, but also does not work.

Your suggestions seem logical so I am at a loss as to why it is not working for me.

Many thanks.

Intriguing. I think my signs are the correct way around. Reasons it might not work:

The "Show error alert..." tick box on the Error Alert tab is not ticked.

Your range names in the formulae do not match the ones you have created for A1 and A2. Unlikely as it should not accept an invalid name but the range name might already exist.

Data validation has not been applied to the cell being used for input

Just for the avoidance of doubt, on the Settings tab, "Allow:" should be set to "Custom" and "Formula:" to "=ABS(Variance)<=Tolerance"

Even if the signs are the wrong way around I have tested using input numbers both below and above 2 so one way or another the pop would appear.; and it doesn't.

The "Show error alert..." tick box on the Error Alert tab is not ticked. (I checked - no difference)

Your range names in the formulae do not match the ones you have created for A1 and A2. Unlikely as it should not accept an invalid name but the range name might already exist. (I checked - no difference)

Data validation has not been applied to the cell being used for input (I checked - no difference)

Just for the avoidance of doubt, on the Settings tab, "Allow:" should be set to "Custom" and "Formula:" to "=ABS(Variance)<=Tolerance" (I checked - no difference)

Bizarre!

If you have actually got this to work perhaps if you can create an example on a spreadsheet and somehow are able to share it?

Paul's formula =ABS(A1)<=A2 works fine using ordinary spreadsheet cells, providing you only ever use 0 or a positive number in cell A2. The < in his formula is the correct way round - it doesn't work at all with a >

I guess you could try that to satisfy yourself that the formula's good, which must narrow it down to your settings. I'm out on that one.

I have two ways to approach this. I get the feeling that you could be working anywhere in the worksheet when the target changes, and that that is when a warning is required.

So, two potentials -

1) Conditional formatting - with two rules - both will apply the formatting to every cell in column A. So please adjust the range if commonly working away from anywhere in column A.

=$a$1>2 applied range: $a:$a

=$a$1<-2applied range: $a$a

So, the range $a:$z will work for every column in that range.

2) Some code which will fire every time the worksheet calculates (i.e. every time a cell is changed)

Private Sub Worksheet_Calculate()

Application.DisplayStatusBar = True

Dim keycell As Range

Set keycell = Range("A1")

On Error GoTo InputErrors:

If Not Intersect(keycell, Range("A1")) Is Nothing Then

If keycell > 2 Then

Application.StatusBar = "A1>2"

MsgBox "*************" & vbCrLf & "A1 > 2" & vbCrLf & "*************"

Else

If keycell < -2 Then

Application.StatusBar = "A1<-2"

MsgBox "<<<<<<<<<<<<" & vbCrLf & "A1 <-2" & vbCrLf & "<<<<<<<<<<<<"

End If

End If

Application.StatusBar = False

End If

InputErrors:

If Err.Number = 13 Then MsgBox "Check that all input data cells only contain numeric values," & vbCrLf & "and not text." & vbCrLf & "Replace the text with a numeric value."

End Sub

Mike