| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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") |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find value in array | Excel Worksheet Functions | |||
| Formula query | Excel Discussion (Misc queries) | |||
| "IF" Formula Query... | Excel Worksheet Functions | |||
| "IF" Formula Query | Excel Worksheet Functions | |||
| AHHHH-Get Data from Multiple Excel workbooks | Excel Discussion (Misc queries) | |||
Linear Mode
