[Solved] data validation problem

Unfortunatley data validation does not work for formulas or copying data for that matter.

I can only suggest the follwoing methods. Other people here may have more ideas too.

1. use Conditional foramtiing on the cell to highlight it when the percentage figure is exceeded. and/or
2. Use an if statement elsewhere on the sheet that will dispaly a BIG UGLY ERROR message if the percentage is exceeded.
3. Write an event procedure to continuously check the percentage value and display an message or perform some custom action when the percentage is exceeded.
4. Place limits on the cells that feed your formula (I11 H11) using data validation.
eg I11 must be less than or equal to 1.1* H11,
H11 must be less less than or equal to I11/1.1

Hope it helps...

You could try playing around with the formula Validation Criteria however I think you'll find that the actual cell (H15) needs to be actively changed for Excel to trigger the Data validation. (I could be wrong though)

You could also try using condition formating as per attached example to highlight that the value has exceeded a set range.

Another though just came to me which involves a VBA solution to your problem. We could use a worksheet event to trigger the validation, I have included this in the attached example also.

AJW

Can't figure out how to attach the file so the code is as below:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = Range("TriggerValidation1").Address Then
GoTo 10
ElseIf Target.Address = Range("TriggerValidation2").Address Then
GoTo 10
End If
Exit Sub
10 Application.ScreenUpdating = False
Application.Range("H15").Activate
SendKeys "{F2}", True
SendKeys "{Enter}", True
Application.ScreenUpdating = True
End Sub

Place this in the code Worksheet code area and assign the range names TriggerValidation1 and TriggerValidation2 to your H11 & I11 cells.

Hit the escape key twice to cancel out of the validation message.

AJW

The attachment capability is down for now :(. I know the mod's and admin's are feverishly trying to resolve the issue though. Keep your fingers crossed that it may soon be fixed.

HTH
:usaflag:

Try the following in the worksheet module for sheet 1 in your book1.

Just a simple message box as requested.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Range("FY05_addition_percentage").Value > 1.1 Then
MsgBox "That number is ugly" & vbCrLf & "Please find a smaller, better looking figure.", _
vbExclamation, "Please edit the last number"
End If
End Sub