Set a data validation parameter

Data validation, set warning message to given parameters

Didn't find your answer?

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.

Avatar
By I'msorryIhaven'taclue
09th Dec 2020 17:51

mung1 wrote:

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 (0)
avatar
By User deleted
09th Dec 2020 19:04

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.

Thanks (0)
Replying to User deleted:
Avatar
By I'msorryIhaven'taclue
10th Dec 2020 10:43

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.

Thanks (0)
avatar
By LAMBERTCLERICAL
10th Dec 2020 11:53

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

Thanks (0)
Replying to LAMBERTCLERICAL:
avatar
By User deleted
10th Dec 2020 15:59

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.

Thanks (0)
Replying to User deleted:
avatar
By paulwakefield1
10th Dec 2020 16:59

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?

Thanks (0)
avatar
By paulwakefield1
10th Dec 2020 12:03

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?

Thanks (0)
Replying to paulwakefield1:
avatar
By User deleted
10th Dec 2020 19:03

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.

Thanks (0)
Replying to User deleted:
avatar
By paulwakefield1
10th Dec 2020 19:46

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"

Thanks (0)
Replying to paulwakefield1:
avatar
By User deleted
10th Dec 2020 20:09

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?

Thanks (0)
Replying to User deleted:
Avatar
By I'msorryIhaven'taclue
11th Dec 2020 13:33

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.

Thanks (0)
avatar
By xluser
13th Jan 2021 22:38

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

Thanks (0)