IF formula query - Help!!
 

I am trying to set up a formula which will validate if cells (non consecutive
cells) contain a value or not (value could be figures/ dates or text). If any
of the 9 cells do not contain a value then I want a value of "error" to
appear. If they all contain a value then I want a value of "OK" to be
displayed. This is what I have got from the knowledge base but I cannot get
this to work and I know I might have difficulty with the 9 statements as I
understand the maximum is 7. This is the formula I have been using:

=IF(C20,"ok","Error",IF(D20,"ok","Error",IF(E20 ,"ok","Error",IF(F20,"ok","Error",IF(G20,"ok","E rror",IF(K20,"ok","Error",IF(L20,"ok","Error",IF (O20,"ok","Error"))))))))

It works OK when the values returned after each statement are difference but
because I want an overall value of OK or Error (and each cell needs to be
validated on this basis) I think I am confusing it.

Can anyone help?

Thanks

IF formula query - Help!!
 

=IF(OR(C20,D20,E20,F20,G20,K20,L20,O20),"o k","Error")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Sassie" wrote in message
...
I am trying to set up a formula which will validate if cells (non
consecutive
cells) contain a value or not (value could be figures/ dates or text). If
any
of the 9 cells do not contain a value then I want a value of "error" to
appear. If they all contain a value then I want a value of "OK" to be
displayed. This is what I have got from the knowledge base but I cannot
get
this to work and I know I might have difficulty with the 9 statements as I
understand the maximum is 7. This is the formula I have been using:


=IF(C20,"ok","Error",IF(D20,"ok","Error",IF(E20 ,"ok","Error",IF(F20,"ok"
,"Error",IF(G20,"ok","Error",IF(K20,"ok","Error" ,IF(L20,"ok","Error",IF(O
20,"ok","Error"))))))))

It works OK when the values returned after each statement are difference
but
because I want an overall value of OK or Error (and each cell needs to be
validated on this basis) I think I am confusing it.

Can anyone help?

Thanks

IF formula query - Help!!
 

Thanks - that is great! you have been really helpful.

Cheers

Sassie

"Bob Phillips" wrote:

=IF(OR(C20,D20,E20,F20,G20,K20,L20,O20),"o k","Error")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Sassie" wrote in message
...
I am trying to set up a formula which will validate if cells (non
consecutive
cells) contain a value or not (value could be figures/ dates or text). If
any
of the 9 cells do not contain a value then I want a value of "error" to
appear. If they all contain a value then I want a value of "OK" to be
displayed. This is what I have got from the knowledge base but I cannot
get
this to work and I know I might have difficulty with the 9 statements as I
understand the maximum is 7. This is the formula I have been using:


=IF(C20,"ok","Error",IF(D20,"ok","Error",IF(E20 ,"ok","Error",IF(F20,"ok"
,"Error",IF(G20,"ok","Error",IF(K20,"ok","Error" ,IF(L20,"ok","Error",IF(O
20,"ok","Error"))))))))

It works OK when the values returned after each statement are difference
but
because I want an overall value of OK or Error (and each cell needs to be
validated on this basis) I think I am confusing it.

Can anyone help?

Thanks

IF formula query - Help!!
 

The OP said that he wanted ALL the cells to have a value before displaying
OK.

Try
=IF(OR(C2="",D2="",E2="",F2="",G2="",K2="",L2="",O 2=""),"Error","OK")
--
David Biddulph

"Bob Phillips" wrote in message
...
=IF(OR(C20,D20,E20,F20,G20,K20,L20,O20),"o k","Error")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Sassie" wrote in message
...
I am trying to set up a formula which will validate if cells (non
consecutive
cells) contain a value or not (value could be figures/ dates or text). If
any
of the 9 cells do not contain a value then I want a value of "error" to
appear. If they all contain a value then I want a value of "OK" to be
displayed. This is what I have got from the knowledge base but I cannot
get
this to work and I know I might have difficulty with the 9 statements as
I
understand the maximum is 7. This is the formula I have been using:


=IF(C20,"ok","Error",IF(D20,"ok","Error",IF(E20 ,"ok","Error",IF(F20,"ok"
,"Error",IF(G20,"ok","Error",IF(K20,"ok","Error" ,IF(L20,"ok","Error",IF(O
20,"ok","Error"))))))))

It works OK when the values returned after each statement are difference
but
because I want an overall value of OK or Error (and each cell needs to be
validated on this basis) I think I am confusing it.

Can anyone help?

Thanks

IF formula query - Help!!
 

But his formula was definitely OR, and he seems happy. Sometimes you get
conflicting statements, so you take a punt. I took a punt.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"David Biddulph" wrote in message
...
The OP said that he wanted ALL the cells to have a value before displaying
OK.

Try
=IF(OR(C2="",D2="",E2="",F2="",G2="",K2="",L2="",O 2=""),"Error","OK")
--
David Biddulph

"Bob Phillips" wrote in message
...
=IF(OR(C20,D20,E20,F20,G20,K20,L20,O20),"o k","Error")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Sassie" wrote in message
...
I am trying to set up a formula which will validate if cells (non
consecutive
cells) contain a value or not (value could be figures/ dates or text).
If
any
of the 9 cells do not contain a value then I want a value of "error" to
appear. If they all contain a value then I want a value of "OK" to be
displayed. This is what I have got from the knowledge base but I cannot
get
this to work and I know I might have difficulty with the 9 statements
as
I
understand the maximum is 7. This is the formula I have been using:



=IF(C20,"ok","Error",IF(D20,"ok","Error",IF(E20 ,"ok","Error",IF(F20,"ok"

,"Error",IF(G20,"ok","Error",IF(K20,"ok","Error" ,IF(L20,"ok","Error",IF(O
20,"ok","Error"))))))))

It works OK when the values returned after each statement are
difference
but
because I want an overall value of OK or Error (and each cell needs to
be
validated on this basis) I think I am confusing it.

Can anyone help?

Thanks

IF formula query - Help!!
 

I am trying to set up a formula which will validate if cells (non consecutive
cells) contain a value or not (value could be figures/ dates or text). If any
of the 9 cells do not contain a value then I want a value of "error" to
appear. If they all contain a value then I want a value of "OK" to be
displayed. This is what I have got from the knowledge base but I cannot
get

"contain a value or not (value could be figures/ dates or text)"

lets hope that the figure needed to be placed in any cell is non-negative
and place as hard figures (no source refs.)

happy holiday

=IF(OR(C2="",D2="",E2="",F2="",G2="",K2="",L2="",O 2=""),"Error","OK")