IF function problem

[Krista]

I was hoping someone would be able to help me with the
following problem. I have a value (0986) which, when I
put it into cell c5, i want the total in cell e5 to be no
more than 0.9. If the total entered is any more than 0.9,
I want an error message to display. (I also don't want
you to be able to continue until the amount is <0.9) I
have tried various IF functions, as well as COUNTIF, but
I can't seem to get anything to work! Any help would be
greatly appreciated!!

thanks

 

[JE McGimpsey]

What is the relationship between E5 and C5?

You may be able to use validation. Assume the formula in E5 is

=C5/1000

Then with C5 selected, choose Data/Validation and enter

Allow: Custom
Formula: =E5<=0.9

then enter an appropriate error message.

Note that Validation can be overwritten by pasting into the cell.

 

[Krista]

that formula kind of works, but i think you need a little
more info!

cell c5 and e5 will always be changing. column c is for a
code, and column e is for hours payable with that code. i
need it so, if 0986 is entered anywhere in column c, then
the total in the corresponding cell in column e cannot
exceed 0.9. if more than 0.9 is entered, i want the error
message to display (but only when it is more than 0.9,
using code 0986!)

its kind of confusing explaining it i know but do you
think it is possible? thankyou for your help so far!

 

[JE McGimpsey]

OK. Assuming that column C will always be filled before column E, select
column E (with E1 the active cell) and choose Data/Validation. Enter:

Allow: Custom
Formula: =IF(C1="0986",E1<=0.9,TRUE)

This assumes that column C is formatted as Text. If 0986 is a number
instead, delete the quotes.

 

[Krista]

you are an absolute genious! it works perfectly! thankyou
so much!

Krista

 

[Krista]

is it possible to do this, but put it in an inactive cell,
so you can set the whole column to work. and if you do use
inactive cells, can you put more than one rule on a column?

thanks