Formula problem

[Tony Miall]

Morning all,

In a worksheet called MICROMETER I have a range I3:I40 in which each cell of that range has a result of either "OK" or "OVERDUE"

In another worksheet (called FRONT PAGE) I want cell B3 to show the result "OK" only if all of the cells I3:I40 in MICROMETER = OK, if one of the cells in the range does not = OK I want cell B3 in FRONT PAGE to = OVERDUE.

I did this with the following formula :

=IF(MICROMETER!$I$3:$I$40="OK", "OK", IF(NOT(MICROMETER!I3:$I$40="OK"),"OVERDUE",""))

this seems to work.

However when I apply the same formula

=IF(VERNIER!$I$3:$I$40="OK", "OK", IF(NOT(VERNIER!I4:$I$40="OK"),"OVERDUE",""))

in cell B4 in FRONT PAGE to look at another worksheet called VERNIER it doesn't work and I can't understand why??

Any help much appreciated as usual.

 

[T. Valko]

Morning all,

In a worksheet called MICROMETER I have a range I3:I40 in which each cell of that range has a result of either "OK" or "OVERDUE"

In another worksheet (called FRONT PAGE) I want cell B3 to show the result "OK" only if all of the cells I3:I40 in MICROMETER = OK, if one of the cells in the range does not = OK I want cell B3 in FRONT PAGE to = OVERDUE.

I did this with the following formula :

=IF(MICROMETER!$I$3:$I$40="OK", "OK", IF(NOT(MICROMETER!I3:$I$40="OK"),"OVERDUE",""))

this seems to work.

However when I apply the same formula

=IF(VERNIER!$I$3:$I$40="OK", "OK", IF(NOT(VERNIER!I4:$I$40="OK"),"OVERDUE",""))

in cell B4 in FRONT PAGE to look at another worksheet called VERNIER it doesn't work and I can't understand why??

Any help much appreciated as usual.

What if there are empty cells in the range?

 

[Tony Miall]

T. Valko,

There are only ever 2 results OK and OVERDUE

 

[T. Valko]

T. Valko,

There are only ever 2 results OK and OVERDUE

OK, try this...

=IF(COUNTIF(MICROMETER!$I$3:$I$40,"OVERDUE"),"OVERDUE","OK")

 

[Tony Miall]

Thanks Valko,

Yours didn't quite work but ended up with this

=IF(COUNTIF(VERNIER!$I$3:$I$40,"OK")<COUNTA(VERNIER!$I$3:$I$40),"OVERDUE","OK")

Thanks for your help

 

[Tony Miall]

sorry this

=IF(COUNTIF(VERNIER!$I$3:$I$40,"OK")<COUNTA(VERNIER!$I$3:$I$40),"OVERDUE","OK")

 

[T. Valko]

sorry this


=IF(COUNTIF(VERNIER!$I$3:$I$40,"OK")<?XML:NAMESPACE PREFIX = COUNTA(VERNIER!$I$3 /><COUNTA(VERNIER!$I$3:$I$40),"OVERDUE","OK")< p>

Hmmm...

Are you sure that does what you want?

That doesn't make sure EVERY cell in the range contains OK.

Maybe I misunderstood what you want.


</COUNTA(VERNIER!$I$3:$I$40),"OVERDUE","OK")<>

 

[Tony Miall]

Not sure whats going on here but this is what I have been trying to paste hope it works this time

=IF(COUNTIF(VERNIER!$I$3:$I$40,"OK")<COUNTA(VERNIER!$I$3:$I$40),"OVERDUE","OK")

thanks again

 

[Tony Miall]

What I'm pasting doesn't seem to be coming through on the post???

 

[Marcelo Branco]

What I'm pasting doesn't seem to be coming through on the post???

If you are using a lessthan symbol < put spaces around it, otherwise the forum software takes it as a html tag

M.