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

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

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?

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.

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