Data validation with a cell value linked to the same cell.

[rodolform]

I Think that my post name is very confusing but I'll explain.

I´m doing a data validation in cell f1 usig the data validation option
the value entered in cell f1 need to be equal or greatter than th
value in cell b2 and cell b2 is calculated with the formula =a2+c4-f1.

What I'm trying to do is to prevent to enter a value in cell f1 tha
I'll make cell b2 negative.

But its not working, any suggetsions???

Thanks.
Rodolf

 

[JE McGimpsey]

Change your CF criterion to

Formula is = F1 <= (A2+C4)

 

[Peo Sjoblom]

Use custom and this formula

=F1<=A2+C4

OTOH you say that F1 needs to be greater or equal to B2
but I assume that was a thought error because if F1 is 0,
A2 and C4 are 10, that means B2 is 20, now if F1 is greater than 20
then B2 will be negative! So I assumed you meant less than or equal to
A2+C4

 

[rodolform]

Thank you all:
You are correct I did mean less than or equal.

I have made the correction of the formula but its not working, i
"fails" when the value of f1 hits half of the value of b2.

Somehow f1 is involved in the value the value of c4 (c4 is the sum o
all values within the column f that meets certain criteria).

Any ideas??

 

[JE McGimpsey]

It's impossible to have any idea what's wrong if you don't post the
formula in C4, along with, perhaps, some typical values.

 

[rodolform]

Here it goes:

c4 formula =sumif(g1:g20,"t1","f1:f20)

so with this values:

a2=80 (this value will be constant)
c4=30 (that is because f2, f5, and f18 meet the criteria)
and f1= 40
with this values works all right. Because f1 is less than a2+c4

but if c4=70 because (f1, f2,f5 and f18 meet the criteria)
it goes and does the calculation and returns with the mesagge that
have provied to show when an error is encountered