Data Validation based on another cell calculation? (Excel 2007)

[KJ2607]

Hi There

Is it possible to apply Data Validation to an empty cell based on the calculations in another cell?

I currently have the following:

H6 contains total hours worked in one shift (which is calculated with a formula)
I6 contains total journey time (which is manually entered)
J6 Contains the total of H6:I6 (again calculated with a formula)

In K6 I would like to apply data validation so the answer in J6 needs to be less than or equal to 14:00 (or 0.58 in numerical terms)

I've tried a few variations including

=J6<=0.58
=IF(J6<=0.58,True,False)
=(H6:I6)<=0.58

But I just can't seem to get it right

Any Suggestions would be greatly appreciated

 

[Snakehips]

KJ2607,

Try Data Validation - Settings tab

Allow Decimal Data between - Minimum 0 (or what suits) Maximum =J6


Hope that helps.

 

[KJ2607]

Hi Snakehips

Thanks for your suggestion - I'm a bit confused with how it works though - if J6 calculates to above 0.58 I need the alert to pop up - so when I enter Maximum=J6 will it not just accept whatever is calculated in J6 regardless of whether it is above 0.58 or not?

Sorry if I'm being daft

 

[Snakehips]

Hi Snakehips

Sorry if I'm being daft

KJ, believe me you have not got a monopoly on being daft!

Treat me as an idiot and you won't go far wrong. I thought you were trying to limit input in K6 to<= J6.

More musings of an idiot......
As J6 is the result of a formula it cannot be subject to data validation in he normal way.

Are you wanting eg K6 to display "< Too High!!" if J6 resolves to > 0.58?

If so then formula in K6 .... =IF(J6>0.58,"< Too High!!!","")

Or do you want to just limit the value in J6 to a max of 0.58?

eg =MIN(H6+I6,0.58)


Or ..............?

 

[KJ2607]

They're both good suggestions

After a night off and stepping away from the screen I've came in today and taken a different approach....I got rid of the J6 and entered Data Validation into I6 using the custom formula =IF(I6+H6)<0.58,TRUE,FALSE) and I get my pop up!

Thanks again for your help