Feb 20 2020 07:09 AM
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
Feb 20 2020 07:29 AM - edited Feb 20 2020 07:31 AM
Hello @Steve-SDC,
If you wish to use Data validation then:
Feb 20 2020 11:28 AM
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?
Feb 20 2020 11:47 AM
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?
Feb 20 2020 12:01 PM
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.
Feb 20 2020 12:31 PM
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
Feb 20 2020 01:09 PM
Feb 20 2020 01:15 PM
I mean such data validation
for both cells. User could enter to these cell manually any values, and they will receive alert if formula calculation result exceed value of the cell C10.
Feb 20 2020 01:19 PM
Feb 20 2020 01:30 PM
Let assume we have correct result at the beginning
Now we are truing to enter 55 into K24
With this we have two options - enter correct value to K24 or cancel and return back to initial data.
Or I misunderstood and you consider another scenario?
Feb 20 2020 01:37 PM
Sergio - Thanks Again! I selected both J22 & K24, then applied the formula to both, but as example shows, it still allowed K25 to exceed value of C10? See attached image which shows the Cells and DV box
Steve
Feb 20 2020 01:39 PM
Yes, the example you have shown matches mine, but I am not getting the error flagged properly @Sergei Baklan
Feb 20 2020 01:44 PM
SolutionFeb 20 2020 02:02 PM
You Da Man!! Thanks so much. My Problem: I selected both J22 and K24 and Jointly set the Data Validation parameters. Once that I Cleared DV, then re-set the DV Parameters individually for each cell, it works! I am (obviously) not that well-versed with formulas - I really appreciate your patience and sending an Excel file as an example. THANKS AGAIN!!
Steve
Feb 20 2020 02:38 PM
Steve, great to know you sorted this out, glad to help.
Feb 20 2020 01:44 PM
SolutionI can't reproduce that
Please check attached file with the sample.