• 670K Members
• 7,099 Online
• 826K Conversations
SOLVED

## Excel - Data Validation

Highlighted
Occasional Contributor

# 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

15 Replies
Highlighted

# Re: Excel - Data Validation

You may use

``=MIN(J22*K24+1,C10)``
Highlighted

# Re: Excel - Data Validation

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

# Re: Excel - Data Validation

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?

Highlighted

# Re: Excel - Data Validation

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?

Highlighted

# Re: Excel - Data Validation

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.

Highlighted

# Re: Excel - Data Validation

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``

Highlighted

# Re: Excel - Data Validation

Sergio, do you mean use that syntax in the Data Validation box? If I use it in cells J22 and K24 I can no longer enter them manually. I want the User to enter them manually - and simply check after they have been entered by using K25 for multiplying them, result of K25 must be <= C10. ??
Highlighted

# Re: Excel - Data Validation

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.

Highlighted

# Re: Excel - Data Validation

Thanks - tried that. Still did not work. I want the User to manually enter J22 and K24. The Result is calculated to K25. But I want an error message (or dis-allow K25 to be populated) IF K25 is > C10.
Highlighted

# Re: Excel - Data Validation

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?

Highlighted

# Re: Excel - Data Validation

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

Highlighted

# Re: Excel - Data Validation

Yes, the example you have shown matches mine, but I am not getting the error flagged properly @Sergei Baklan

Highlighted
Solution

# Re: Excel - Data Validation

I can't reproduce that

Please check attached file with the sample.

Highlighted

# Re: Excel - Data Validation

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

Highlighted

# Re: Excel - Data Validation

Steve, great to know you sorted this out, glad to help.