Custom Data Validation error message using value from a cell

Harryf

New Member
Joined
Aug 31, 2013
Messages
44
Hi everybody!

In a spreadsheet, cell A1 calculates an amount. This amount is the maximum allowed that an investor may withdraw from an investment and is shown to the investor in Cell A1 as a "warning" of some kind. I actually have text running from B1 across various columns to the right stating that the value shown in A1 is the maximum allowed.

In cell A2, I ask the investor the actual amount that s/he would like to withdraw. But people don't always read instructions and someone invariably enters an amount exceeding the value indicated in A1...

So set up Data Validation in A2 to ask the investor to focus and to re-enter the amount but to limit it to the amount shown in A1, should the amount entered in A2 be bigger than the amount in A1 - but I can only use the words "cell A1" in the error message and not the actual amount, which will differ depending on the amount invested.

Let's assume the amount in A1 is calculated as $1, 000. This is the maximum allowed. Should an investor enter anything bigger than that in A2, my current Data Validation error message would pop up. It currently reads "The amount entered in this cell may not exceed the value indicated in cell A1. Please re-enter."

I would like it to read "The amount entered may not exceed $1, 000. Please re-enter", $1, 000 being the variable value drawn from cell A1.

Is this even possible?

Looking forward to your replies!

Cheers!

Harry
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

sparky2205

Active Member
Joined
Feb 6, 2013
Messages
350
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I don't believe this is possible. As far as I'm aware you can't link a cell to a Data Validation message.
The best you can probably achieve is to put your warning in the Input Message. That way when the user selects cell A2 to make an entry the message is popped up. In my experience users are more likely to read a popup message than text that is permanently displayed.
 

sparky2205

Active Member
Joined
Feb 6, 2013
Messages
350
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
An alternative I thought of.
In cell B1: =IF(A2>A1,"The amount entered exceeds the max amount allowed of " & DOLLAR(A1,0),"")
You could format the message to appear in red font. The message will only appear when the max amount is exceeded.
The user is more likely to read a message that suddenly appears in red.
The drawback is this won't prevent the user entering the greater value.
Just an option.

You could also use Conditional Formatting to make the font colour of A2 red under the same conditions i.e. A2>A1
 

Harryf

New Member
Joined
Aug 31, 2013
Messages
44
Thanks Sparky!

I guessed as much... ;) I actually thought to remove any reference to the reason for the figure in cell A1 when reading your post, but I guess it may cause more confusion if I just place a number randomly in a spreadsheet without indicating what it is... I do, however, believe that you are right and that someone may wonder why a lone figure is there, but once there is text next to it, not even care to read the text... Strange how our minds work... hehe

I'll just keep the error message, then. Thanks again!
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,393
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

@Harryf If you are able to use vba then try pasting this into the sheet's code module within the vba editor..

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A2")) Is Nothing Then Exit Sub
If Target.Count = 1 Then

    Limit = FormatCurrency(Range("A1"))
     With Range("A2").Validation
            .Delete
            .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, Formula1:="0", Formula2:="=A1"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""   '<<< Edit to suit
            .ErrorTitle = "Oi Mush!  Can You NOT Read?"  '<<< Edit to suit
            .InputMessage = "" '<<< Edit to suit
            .ErrorMessage = _
            "The amount entered may not exceed   " & FormatCurrency(Range("A1")) & "  Please re-enter"   '<<< Edit to suit
            .ShowInput = True
            .ShowError = True
        End With
End If
End Sub

Hope that helps.
 

Harryf

New Member
Joined
Aug 31, 2013
Messages
44
Sorry... Missed your second post Sparky... Good idea about the IF function and the conditional formatting, but like I said above, I have to force a correct number in A2 and the message won't prevent someone ignoring it... Cheers!
 
Last edited:

Harryf

New Member
Joined
Aug 31, 2013
Messages
44

ADVERTISEMENT

Hello Snakehips!

Great!!! Thank you very much!! I changed the cell references and error messages to suit my actual spreadsheet and it works like an absolute charm!

There you go Sparky! We met the Master... 😉😉
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,393
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hi Harry ! Pleased we could help.

You may wish to try the following variation that should give a popup message on selection of the input cell.

VBA Code:
Private Sub Worksheet_Calculate()
Range("A2").Validation.InputMessage = "PLEASE Enter an amount NOT greater than  " & FormatCurrency(Range("A1")) '<<< Edit to suit
End Sub

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A2")) Is Nothing Then Exit Sub
If Target.Count = 1 Then

    Limit = FormatCurrency(Range("A1"))
     With Range("A2").Validation
            .Delete
            .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, Formula1:="0", Formula2:="=A1"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""   '<<< Edit to suit
            .ErrorTitle = "Oi Mush!  Can You NOT Read?"  '<<< Edit to suit
            .InputMessage = "PLEASE Enter an amount NOT greater than  " & FormatCurrency(Range("A1").Value) '<<< Edit to suit
            .ErrorMessage = _
            "The amount entered may not exceed   " & FormatCurrency(Range("A1")) & "  Please re-enter"   '<<< Edit to suit
            .ShowInput = True
            .ShowError = True
        End With
End If
End Sub
 

Forum statistics

Threads
1,148,282
Messages
5,745,836
Members
423,981
Latest member
ph1l

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top