Excel 2007 Data Validation Query - sum of two cells less than a set number

[kj2607]

Hello Everyone!

I am wanting to create a validation rule so when two cells are added the together the answer must be less than or equal to 14:00 - otherwise a message box will appear

So for example if H7+I7 gives an answer of 13:00 in J7, that's OK, but if the answer is 15:00, the error box will appear. I know that data validation doesnt work on a cell that already has a formula so I'm hoping to use K7 for the Validation and then hide the column

I'm sure there's a simple answer for this but I've clearly being staring at this for too long now



Thanks in Advance

[hafizimran]

Select the cells on which you want to apply validation, suppose (H7:I17)
In validation box select Allow = Custome and Put this formula

Please Login or Register  to view this content.

[kj2607]

Hi Thanks for your suggestion.

I've entered the above validation into K7 but it doesnt appear to work when I enter my data into H7 and I7 - any suggestions?

is it possible to use an Indirect function in the validation so if say, J7<=14:00 (or 0.58 im numerical format) the alert will appear?

[hafizimran]

You have to apply the validation formula on both cells being summed up in formula, with absolute Column Ref and relative Row Ref. Attn:formula given above in my previous post.

[kj2607]

I may be mis-understanding your instructions - but I can't apply data validation to H7 as the data is not entered manually but calculated from a formula, hence why I was using a blank cell in K7?

I've attached a sample of my spreadsheet to explain a bit more....