[Solved] data validation problem

  • ok,, here is my issue,, I have a cell (H15) I want to prevent from being over 110%, so on the data validation I put in decimal and maximum 1.1, it works fine if I just enter into the cell.. But I have a formula in the cell that says =I11/H11, if the result of my formula comes out to greater than 110% it doesn't prevent the cell from changing. is there a way to use the data validation based on a formula's result, not only input? Hope this is clear. Thanks again for any help!

  • 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.


  • 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
    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.


  • Thanks a lot guys! I will give all of these methods a try today or this weekend and let you know how they work out in my sheet.. appreciate the suggestions!!

  • ok,, need a little more help on this one... I tried using AJW's idea,, but since the % is a locked cell, it didn't work, so I had the code unprotect the sheet first then run it, but I wasn't crazy about it, because if I just hit cancel on the error it allowed the cell change.. Also, my "I11" cell has a sum formula in it so again a prob... Thanks though AJW!!!

    Kieran, how would I just write an if statement that also brings up an msgbox?

    I am going to attach the actual sheet I created, so the whole I11, H11 cells are irrelevent... This is the new setup...
    The only place in this that allows data entry is noted and I also noted the % I don't want over 110%, ideally it would clear whatever data one entered to make it go over as well... Check out the sheet and let me know if you need more info from me! Thanks a lot for all the help!!!


  • 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


  • Ahhh so simple! I was trying to do this and I think the only thing I was missing was the .value after the Range... Thanks so much for taking the time to help! Appreciate it!!!!!!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!