Forum Discussion

Steve-SDC's avatar
Steve-SDC
Copper Contributor
Feb 20, 2020
Solved

Excel - Data Validation

In Excel - I have a simple calculation for Cell K25:              =((J22*K24)+1)         But I want to enforce a rule on the RESULT: Cell K25 must Also be <= C10      Can you help me with the required formula/Data Validation for this cell? I can’t get my Data Validation error message to appear? My DV input screen is in image file below. Thanks! Steve-SDC

excel

15 Replies

  • PReagan's avatar
    PReagan
    Bronze Contributor
    Feb 20, 2020

    Hello Steve-SDC,

     

    If you wish to use Data validation then:

    1. Set Data Validation to "less than or equal to"
    2. Decide whether or not to "ignore blank" by checking or unchecking box
    3. In Error Alert tab, ensure that "Show error alert after invalid...." is checked
      1. Insert an error message if you wish. 
    • Steve-SDC's avatar
      Steve-SDC
      Copper Contributor
      Feb 20, 2020

      PReagan 

      Thanks - I had already tried that, but does not work. When the  cell that is being calculated exceeds the value of cell C10, it is still populated. I want it to generate my error message in the event that the result is not Less Than value contained in (Max) cell C10. Further ideas?

      • PReagan's avatar
        PReagan
        Bronze Contributor
        Feb 20, 2020

        Hello Steve-SDC,

         

        Try to recalculate the cell with data validation after you set the data validation. If you set the data validation after you already calculated the cell, then it will not show an error.

    • Steve-SDC's avatar
      Steve-SDC
      Copper Contributor
      Feb 20, 2020

      SergeiBaklan 

      Thanks Sergio, but that didn't do it - the recommended syntax meant it just used the value for cell C10 since it was the MIN (lower) of the 2. I need to Check/validate that my calculation is Less Than C10 - by sending an error message, Tried to use Data Validation but can't get it to work?

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Feb 20, 2020

        Steve-SDC 

        As variant and if J22 and K24 are entered manually, you may apply to each of above cells data validation with the formula

        =($J$22*$K$24+1) <=C10

         

Resources