data validation based on cell value

J

jpzachar

Hello......I hope you can help....thanks in advance!

I would like to have an error message pop up if the result of a formula in
cell N100 is greater than 5. I tried using the data validation and "custom"
selection but could not get it to work. Is this possible?

thanks,
Jeff
 
C

Chip Pearson

Data validation only imposes constraints on what the user can type
into a cell. It has nothing to do with calculated values. The closest
you can get is to use Conditional Formatting to change the color of a
cell based on some criteria.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
R

Rick Rothstein

Can you make use of a VBA solution? Assuming the cells that N100's formula
is dependent on are all located on the same worksheet as N100, right click
the tab at the bottom of that worksheet and select View Code from the popup
menu that appears; then copy/paste the following code into the code window
that appears...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("N100"), Target.Dependents) Is Nothing Then
If Target.Dependents.Value > 5 Then MsgBox "Cell N100 is too big!"
End If
End Sub

I'm not sure, off the top of my head, whether this can be extended to N100
dependencies located on other worksheets. If that is your situation, let us
know so we can investigate whether a solution can be found or not.
 
Top