Home |
Search |
Today's Posts |
#1
|
|||
|
|||
IF formulas--please help!!
I am trying to create a spreadsheet which others will be using to input data
which hopefully will be user friendly. I am wondering if anyone can help me write these formulas--I haven't been able to figure it out. I am trying to create four 'IF' formulas within one cell that will return an error message to the user if they made a mistake on the spreadsheet or do not complete all of the needed cells. When I put in more than one formula into the one cell, I get an error message. Is it possible to enter in more than one formula into one cell, and if so, any ideas on how it should be written? If this is not possible, are there any other ideas on how I could make this work? Thanks in advance. The basics of what I want to say are below. If B12 is blank, and any of D12 through K12 have an 'x', return ERROR 1: Name Missing; If B12 has text, and any of D12 through K12 are blank, return ERROR 2: Data Missing; If B12 has text, and D12 though K12 "x"1, return ERROR 3: More than one column checked; If c12 has "x" and H12 has "x", return ERROR 4: Wrong data box checked. |
#2
|
|||
|
|||
=IF(ISBLANK(B12),IF(OR(D12="x",K12="x"),"ERROR 1:Name
Missing",IF(COUNTA(D12:K12)<8,"ERROR 2: Data Missing",IF(COUNTIF(D12:K12,"x")1," ERROR 3: More than one column checked",IF(AND(C12="x",H12="x"),"ERROR 4: Wrong data box checked",""))))) I think <g -- HTH RP (remove nothere from the email address if mailing direct) "SJC" wrote in message ... I am trying to create a spreadsheet which others will be using to input data which hopefully will be user friendly. I am wondering if anyone can help me write these formulas--I haven't been able to figure it out. I am trying to create four 'IF' formulas within one cell that will return an error message to the user if they made a mistake on the spreadsheet or do not complete all of the needed cells. When I put in more than one formula into the one cell, I get an error message. Is it possible to enter in more than one formula into one cell, and if so, any ideas on how it should be written? If this is not possible, are there any other ideas on how I could make this work? Thanks in advance. The basics of what I want to say are below. If B12 is blank, and any of D12 through K12 have an 'x', return ERROR 1: Name Missing; If B12 has text, and any of D12 through K12 are blank, return ERROR 2: Data Missing; If B12 has text, and D12 though K12 "x"1, return ERROR 3: More than one column checked; If c12 has "x" and H12 has "x", return ERROR 4: Wrong data box checked. |
#3
|
|||
|
|||
Thank you for your help. That formula thread helps quite a bit, but all four
formulas depend on if b12 is blank as the formula states, which that would apply only for the first error. How then can I add "if b12 has text" for error 2, 3, and 4? Thanks once again. "Bob Phillips" wrote: =IF(ISBLANK(B12),IF(OR(D12="x",K12="x"),"ERROR 1:Name Missing",IF(COUNTA(D12:K12)<8,"ERROR 2: Data Missing",IF(COUNTIF(D12:K12,"x")1," ERROR 3: More than one column checked",IF(AND(C12="x",H12="x"),"ERROR 4: Wrong data box checked",""))))) I think <g -- HTH RP (remove nothere from the email address if mailing direct) "SJC" wrote in message ... I am trying to create a spreadsheet which others will be using to input data which hopefully will be user friendly. I am wondering if anyone can help me write these formulas--I haven't been able to figure it out. I am trying to create four 'IF' formulas within one cell that will return an error message to the user if they made a mistake on the spreadsheet or do not complete all of the needed cells. When I put in more than one formula into the one cell, I get an error message. Is it possible to enter in more than one formula into one cell, and if so, any ideas on how it should be written? If this is not possible, are there any other ideas on how I could make this work? Thanks in advance. The basics of what I want to say are below. If B12 is blank, and any of D12 through K12 have an 'x', return ERROR 1: Name Missing; If B12 has text, and any of D12 through K12 are blank, return ERROR 2: Data Missing; If B12 has text, and D12 though K12 "x"1, return ERROR 3: More than one column checked; If c12 has "x" and H12 has "x", return ERROR 4: Wrong data box checked. |
#4
|
|||
|
|||
Hi SJC
is this what you're after? =IF(ISBLANK(B12),IF(COUNTIF(D12:K12,"x")=0,"ERROR 1: Name Missing",""),IF(COUNTIF(D12:K12,"<"&"")<8,"ERROR 2: Data Missing",IF(COUNTIF(D12:K12,"x")1,"ERROR 3: More than one column checked",IF(AND(C12="x",H12="x"),"ERROR 4: Wrong data box checked","")))) Cheers JulieD "SJC" wrote in message ... Thank you for your help. That formula thread helps quite a bit, but all four formulas depend on if b12 is blank as the formula states, which that would apply only for the first error. How then can I add "if b12 has text" for error 2, 3, and 4? Thanks once again. "Bob Phillips" wrote: =IF(ISBLANK(B12),IF(OR(D12="x",K12="x"),"ERROR 1:Name Missing",IF(COUNTA(D12:K12)<8,"ERROR 2: Data Missing",IF(COUNTIF(D12:K12,"x")1," ERROR 3: More than one column checked",IF(AND(C12="x",H12="x"),"ERROR 4: Wrong data box checked",""))))) I think <g -- HTH RP (remove nothere from the email address if mailing direct) "SJC" wrote in message ... I am trying to create a spreadsheet which others will be using to input data which hopefully will be user friendly. I am wondering if anyone can help me write these formulas--I haven't been able to figure it out. I am trying to create four 'IF' formulas within one cell that will return an error message to the user if they made a mistake on the spreadsheet or do not complete all of the needed cells. When I put in more than one formula into the one cell, I get an error message. Is it possible to enter in more than one formula into one cell, and if so, any ideas on how it should be written? If this is not possible, are there any other ideas on how I could make this work? Thanks in advance. The basics of what I want to say are below. If B12 is blank, and any of D12 through K12 have an 'x', return ERROR 1: Name Missing; If B12 has text, and any of D12 through K12 are blank, return ERROR 2: Data Missing; If B12 has text, and D12 though K12 "x"1, return ERROR 3: More than one column checked; If c12 has "x" and H12 has "x", return ERROR 4: Wrong data box checked. |
#5
|
|||
|
|||
Julie,
Do we not still have a conflict with ERROR1 & ERROR4? For example if C12 and H12 have each got an 'x' with all other cells blank then the conditions are met for ERROR1 but we really want ERROR4. The trouble is that H12 in included in the D12:K12 range. That being the case then moving ERROR 1 to the first test may solve the problem: =IF(AND(C12="x",H12="x"),"ERROR 4: Wrong data box checked",IF(AND(ISBLANK(B12),COUNTIF(D12:K12,"x") =0),"ERROR1: Name Missing",IF(COUNTIF(D12:K12,"<"&"")<8,"ERROR 2: Data Missing",IF(COUNTIF(D12:K12,"x")1,"ERROR 3: More than one column checked","")))) Just a thought Sandy -- to e-mail direct replace @mailinator.com with @tiscali.co.uk "JulieD" wrote in message ... Hi SJC is this what you're after? =IF(ISBLANK(B12),IF(COUNTIF(D12:K12,"x")=0,"ERROR 1: Name Missing",""),IF(COUNTIF(D12:K12,"<"&"")<8,"ERROR 2: Data Missing",IF(COUNTIF(D12:K12,"x")1,"ERROR 3: More than one column checked",IF(AND(C12="x",H12="x"),"ERROR 4: Wrong data box checked","")))) Cheers JulieD "SJC" wrote in message ... Thank you for your help. That formula thread helps quite a bit, but all four formulas depend on if b12 is blank as the formula states, which that would apply only for the first error. How then can I add "if b12 has text" for error 2, 3, and 4? Thanks once again. "Bob Phillips" wrote: =IF(ISBLANK(B12),IF(OR(D12="x",K12="x"),"ERROR 1:Name Missing",IF(COUNTA(D12:K12)<8,"ERROR 2: Data Missing",IF(COUNTIF(D12:K12,"x")1," ERROR 3: More than one column checked",IF(AND(C12="x",H12="x"),"ERROR 4: Wrong data box checked",""))))) I think <g -- HTH RP (remove nothere from the email address if mailing direct) "SJC" wrote in message ... I am trying to create a spreadsheet which others will be using to input data which hopefully will be user friendly. I am wondering if anyone can help me write these formulas--I haven't been able to figure it out. I am trying to create four 'IF' formulas within one cell that will return an error message to the user if they made a mistake on the spreadsheet or do not complete all of the needed cells. When I put in more than one formula into the one cell, I get an error message. Is it possible to enter in more than one formula into one cell, and if so, any ideas on how it should be written? If this is not possible, are there any other ideas on how I could make this work? Thanks in advance. The basics of what I want to say are below. If B12 is blank, and any of D12 through K12 have an 'x', return ERROR 1: Name Missing; If B12 has text, and any of D12 through K12 are blank, return ERROR 2: Data Missing; If B12 has text, and D12 though K12 "x"1, return ERROR 3: More than one column checked; If c12 has "x" and H12 has "x", return ERROR 4: Wrong data box checked. |
#6
|
|||
|
|||
Maybe this
=IF(AND(ISBLANK(B12),OR(D12="x",K12="x")),"ERROR 1:Name Missing",IF(COUNTA(D12:K12)<8,"ERROR 2: Data Missing",IF(COUNTIF(D12:K12,"x")1," ERROR 3: More than one column checked",IF(AND(C12="x",H12="x"),"ERROR 4: Wrong data box checked","")))) -- HTH RP (remove nothere from the email address if mailing direct) "SJC" wrote in message ... Thank you for your help. That formula thread helps quite a bit, but all four formulas depend on if b12 is blank as the formula states, which that would apply only for the first error. How then can I add "if b12 has text" for error 2, 3, and 4? Thanks once again. "Bob Phillips" wrote: =IF(ISBLANK(B12),IF(OR(D12="x",K12="x"),"ERROR 1:Name Missing",IF(COUNTA(D12:K12)<8,"ERROR 2: Data Missing",IF(COUNTIF(D12:K12,"x")1," ERROR 3: More than one column checked",IF(AND(C12="x",H12="x"),"ERROR 4: Wrong data box checked",""))))) I think <g -- HTH RP (remove nothere from the email address if mailing direct) "SJC" wrote in message ... I am trying to create a spreadsheet which others will be using to input data which hopefully will be user friendly. I am wondering if anyone can help me write these formulas--I haven't been able to figure it out. I am trying to create four 'IF' formulas within one cell that will return an error message to the user if they made a mistake on the spreadsheet or do not complete all of the needed cells. When I put in more than one formula into the one cell, I get an error message. Is it possible to enter in more than one formula into one cell, and if so, any ideas on how it should be written? If this is not possible, are there any other ideas on how I could make this work? Thanks in advance. The basics of what I want to say are below. If B12 is blank, and any of D12 through K12 have an 'x', return ERROR 1: Name Missing; If B12 has text, and any of D12 through K12 are blank, return ERROR 2: Data Missing; If B12 has text, and D12 though K12 "x"1, return ERROR 3: More than one column checked; If c12 has "x" and H12 has "x", return ERROR 4: Wrong data box checked. |
#7
|
|||
|
|||
Hi Sandy
as far as i can tell no conflict with error 1 and error 4 as error 1 can only occur if B12 is blank, whereas error 4 can only occur if b12 is not blank. Cheers JulieD "Sandy Mann" wrote in message ... Julie, Do we not still have a conflict with ERROR1 & ERROR4? For example if C12 and H12 have each got an 'x' with all other cells blank then the conditions are met for ERROR1 but we really want ERROR4. The trouble is that H12 in included in the D12:K12 range. That being the case then moving ERROR 1 to the first test may solve the problem: =IF(AND(C12="x",H12="x"),"ERROR 4: Wrong data box checked",IF(AND(ISBLANK(B12),COUNTIF(D12:K12,"x") =0),"ERROR1: Name Missing",IF(COUNTIF(D12:K12,"<"&"")<8,"ERROR 2: Data Missing",IF(COUNTIF(D12:K12,"x")1,"ERROR 3: More than one column checked","")))) Just a thought Sandy -- to e-mail direct replace @mailinator.com with @tiscali.co.uk "JulieD" wrote in message ... Hi SJC is this what you're after? =IF(ISBLANK(B12),IF(COUNTIF(D12:K12,"x")=0,"ERROR 1: Name Missing",""),IF(COUNTIF(D12:K12,"<"&"")<8,"ERROR 2: Data Missing",IF(COUNTIF(D12:K12,"x")1,"ERROR 3: More than one column checked",IF(AND(C12="x",H12="x"),"ERROR 4: Wrong data box checked","")))) Cheers JulieD "SJC" wrote in message ... Thank you for your help. That formula thread helps quite a bit, but all four formulas depend on if b12 is blank as the formula states, which that would apply only for the first error. How then can I add "if b12 has text" for error 2, 3, and 4? Thanks once again. "Bob Phillips" wrote: =IF(ISBLANK(B12),IF(OR(D12="x",K12="x"),"ERROR 1:Name Missing",IF(COUNTA(D12:K12)<8,"ERROR 2: Data Missing",IF(COUNTIF(D12:K12,"x")1," ERROR 3: More than one column checked",IF(AND(C12="x",H12="x"),"ERROR 4: Wrong data box checked",""))))) I think <g -- HTH RP (remove nothere from the email address if mailing direct) "SJC" wrote in message ... I am trying to create a spreadsheet which others will be using to input data which hopefully will be user friendly. I am wondering if anyone can help me write these formulas--I haven't been able to figure it out. I am trying to create four 'IF' formulas within one cell that will return an error message to the user if they made a mistake on the spreadsheet or do not complete all of the needed cells. When I put in more than one formula into the one cell, I get an error message. Is it possible to enter in more than one formula into one cell, and if so, any ideas on how it should be written? If this is not possible, are there any other ideas on how I could make this work? Thanks in advance. The basics of what I want to say are below. If B12 is blank, and any of D12 through K12 have an 'x', return ERROR 1: Name Missing; If B12 has text, and any of D12 through K12 are blank, return ERROR 2: Data Missing; If B12 has text, and D12 though K12 "x"1, return ERROR 3: More than one column checked; If c12 has "x" and H12 has "x", return ERROR 4: Wrong data box checked. |
#8
|
|||
|
|||
Hi Julie,
Yes, you're quite right. With B12 blank and C12 & H12 having x, your formula gives ERROR1 and when you enter a name in B12, it reverts to ERROR4 whereas my rearrangement of your formula gives ERROR4 and then when you correct that by removing the 'x' in C12 reverts to ERROR1. Not exactly a revelation! Regards Sandy -- to e-mail direct replace @mailinator.com with @tiscali.co.uk "JulieD" wrote in message ... Hi Sandy as far as i can tell no conflict with error 1 and error 4 as error 1 can only occur if B12 is blank, whereas error 4 can only occur if b12 is not blank. Cheers JulieD "Sandy Mann" wrote in message ... Julie, Do we not still have a conflict with ERROR1 & ERROR4? For example if C12 and H12 have each got an 'x' with all other cells blank then the conditions are met for ERROR1 but we really want ERROR4. The trouble is that H12 in included in the D12:K12 range. That being the case then moving ERROR 1 to the first test may solve the problem: =IF(AND(C12="x",H12="x"),"ERROR 4: Wrong data box checked",IF(AND(ISBLANK(B12),COUNTIF(D12:K12,"x") =0),"ERROR1: Name Missing",IF(COUNTIF(D12:K12,"<"&"")<8,"ERROR 2: Data Missing",IF(COUNTIF(D12:K12,"x")1,"ERROR 3: More than one column checked","")))) Just a thought Sandy -- to e-mail direct replace @mailinator.com with @tiscali.co.uk "JulieD" wrote in message ... Hi SJC is this what you're after? =IF(ISBLANK(B12),IF(COUNTIF(D12:K12,"x")=0,"ERROR 1: Name Missing",""),IF(COUNTIF(D12:K12,"<"&"")<8,"ERROR 2: Data Missing",IF(COUNTIF(D12:K12,"x")1,"ERROR 3: More than one column checked",IF(AND(C12="x",H12="x"),"ERROR 4: Wrong data box checked","")))) Cheers JulieD "SJC" wrote in message ... Thank you for your help. That formula thread helps quite a bit, but all four formulas depend on if b12 is blank as the formula states, which that would apply only for the first error. How then can I add "if b12 has text" for error 2, 3, and 4? Thanks once again. "Bob Phillips" wrote: =IF(ISBLANK(B12),IF(OR(D12="x",K12="x"),"ERROR 1:Name Missing",IF(COUNTA(D12:K12)<8,"ERROR 2: Data Missing",IF(COUNTIF(D12:K12,"x")1," ERROR 3: More than one column checked",IF(AND(C12="x",H12="x"),"ERROR 4: Wrong data box checked",""))))) I think <g -- HTH RP (remove nothere from the email address if mailing direct) "SJC" wrote in message ... I am trying to create a spreadsheet which others will be using to input data which hopefully will be user friendly. I am wondering if anyone can help me write these formulas--I haven't been able to figure it out. I am trying to create four 'IF' formulas within one cell that will return an error message to the user if they made a mistake on the spreadsheet or do not complete all of the needed cells. When I put in more than one formula into the one cell, I get an error message. Is it possible to enter in more than one formula into one cell, and if so, any ideas on how it should be written? If this is not possible, are there any other ideas on how I could make this work? Thanks in advance. The basics of what I want to say are below. If B12 is blank, and any of D12 through K12 have an 'x', return ERROR 1: Name Missing; If B12 has text, and any of D12 through K12 are blank, return ERROR 2: Data Missing; If B12 has text, and D12 though K12 "x"1, return ERROR 3: More than one column checked; If c12 has "x" and H12 has "x", return ERROR 4: Wrong data box checked. |
#9
|
|||
|
|||
Thank you all so much for your help. All of the formulas are working--I
appreciate you sharing your expertise. I have only one last question. If all fields are blank, meaning the person did not use all of the lines, there still returns one of the error messages. Is there a way to add to the current formula so that if all fields in the line are blank, no error message will be returned? Thanks once again!! "Bob Phillips" wrote: Maybe this =IF(AND(ISBLANK(B12),OR(D12="x",K12="x")),"ERROR 1:Name Missing",IF(COUNTA(D12:K12)<8,"ERROR 2: Data Missing",IF(COUNTIF(D12:K12,"x")1," ERROR 3: More than one column checked",IF(AND(C12="x",H12="x"),"ERROR 4: Wrong data box checked","")))) -- HTH RP (remove nothere from the email address if mailing direct) "SJC" wrote in message ... Thank you for your help. That formula thread helps quite a bit, but all four formulas depend on if b12 is blank as the formula states, which that would apply only for the first error. How then can I add "if b12 has text" for error 2, 3, and 4? Thanks once again. "Bob Phillips" wrote: =IF(ISBLANK(B12),IF(OR(D12="x",K12="x"),"ERROR 1:Name Missing",IF(COUNTA(D12:K12)<8,"ERROR 2: Data Missing",IF(COUNTIF(D12:K12,"x")1," ERROR 3: More than one column checked",IF(AND(C12="x",H12="x"),"ERROR 4: Wrong data box checked",""))))) I think <g -- HTH RP (remove nothere from the email address if mailing direct) "SJC" wrote in message ... I am trying to create a spreadsheet which others will be using to input data which hopefully will be user friendly. I am wondering if anyone can help me write these formulas--I haven't been able to figure it out. I am trying to create four 'IF' formulas within one cell that will return an error message to the user if they made a mistake on the spreadsheet or do not complete all of the needed cells. When I put in more than one formula into the one cell, I get an error message. Is it possible to enter in more than one formula into one cell, and if so, any ideas on how it should be written? If this is not possible, are there any other ideas on how I could make this work? Thanks in advance. The basics of what I want to say are below. If B12 is blank, and any of D12 through K12 have an 'x', return ERROR 1: Name Missing; If B12 has text, and any of D12 through K12 are blank, return ERROR 2: Data Missing; If B12 has text, and D12 though K12 "x"1, return ERROR 3: More than one column checked; If c12 has "x" and H12 has "x", return ERROR 4: Wrong data box checked. |
#10
|
|||
|
|||
=IF(COUNTA(B12:K12)=0,"",IF(AND(ISBLANK(B12),OR(D1 2="x",K12="x")),"ERROR
1:Name Missing",IF(COUNTA(D12:K12)<8,"ERROR 2: Data Missing",IF(COUNTIF(D12:K12,"x")1," ERROR 3: More than one column checked",IF(AND(C12="x",H12="x"),"ERROR 4: Wrong data box checked",""))))) -- HTH RP (remove nothere from the email address if mailing direct) "SJC" wrote in message ... Thank you all so much for your help. All of the formulas are working--I appreciate you sharing your expertise. I have only one last question. If all fields are blank, meaning the person did not use all of the lines, there still returns one of the error messages. Is there a way to add to the current formula so that if all fields in the line are blank, no error message will be returned? Thanks once again!! "Bob Phillips" wrote: Maybe this =IF(AND(ISBLANK(B12),OR(D12="x",K12="x")),"ERROR 1:Name Missing",IF(COUNTA(D12:K12)<8,"ERROR 2: Data Missing",IF(COUNTIF(D12:K12,"x")1," ERROR 3: More than one column checked",IF(AND(C12="x",H12="x"),"ERROR 4: Wrong data box checked","")))) -- HTH RP (remove nothere from the email address if mailing direct) "SJC" wrote in message ... Thank you for your help. That formula thread helps quite a bit, but all four formulas depend on if b12 is blank as the formula states, which that would apply only for the first error. How then can I add "if b12 has text" for error 2, 3, and 4? Thanks once again. "Bob Phillips" wrote: =IF(ISBLANK(B12),IF(OR(D12="x",K12="x"),"ERROR 1:Name Missing",IF(COUNTA(D12:K12)<8,"ERROR 2: Data Missing",IF(COUNTIF(D12:K12,"x")1," ERROR 3: More than one column checked",IF(AND(C12="x",H12="x"),"ERROR 4: Wrong data box checked",""))))) I think <g -- HTH RP (remove nothere from the email address if mailing direct) "SJC" wrote in message ... I am trying to create a spreadsheet which others will be using to input data which hopefully will be user friendly. I am wondering if anyone can help me write these formulas--I haven't been able to figure it out. I am trying to create four 'IF' formulas within one cell that will return an error message to the user if they made a mistake on the spreadsheet or do not complete all of the needed cells. When I put in more than one formula into the one cell, I get an error message. Is it possible to enter in more than one formula into one cell, and if so, any ideas on how it should be written? If this is not possible, are there any other ideas on how I could make this work? Thanks in advance. The basics of what I want to say are below. If B12 is blank, and any of D12 through K12 have an 'x', return ERROR 1: Name Missing; If B12 has text, and any of D12 through K12 are blank, return ERROR 2: Data Missing; If B12 has text, and D12 though K12 "x"1, return ERROR 3: More than one column checked; If c12 has "x" and H12 has "x", return ERROR 4: Wrong data box checked. |
#11
|
|||
|
|||
That is perfect!! Thank you so much!!
"Bob Phillips" wrote: =IF(COUNTA(B12:K12)=0,"",IF(AND(ISBLANK(B12),OR(D1 2="x",K12="x")),"ERROR 1:Name Missing",IF(COUNTA(D12:K12)<8,"ERROR 2: Data Missing",IF(COUNTIF(D12:K12,"x")1," ERROR 3: More than one column checked",IF(AND(C12="x",H12="x"),"ERROR 4: Wrong data box checked",""))))) -- HTH RP (remove nothere from the email address if mailing direct) "SJC" wrote in message ... Thank you all so much for your help. All of the formulas are working--I appreciate you sharing your expertise. I have only one last question. If all fields are blank, meaning the person did not use all of the lines, there still returns one of the error messages. Is there a way to add to the current formula so that if all fields in the line are blank, no error message will be returned? Thanks once again!! "Bob Phillips" wrote: Maybe this =IF(AND(ISBLANK(B12),OR(D12="x",K12="x")),"ERROR 1:Name Missing",IF(COUNTA(D12:K12)<8,"ERROR 2: Data Missing",IF(COUNTIF(D12:K12,"x")1," ERROR 3: More than one column checked",IF(AND(C12="x",H12="x"),"ERROR 4: Wrong data box checked","")))) -- HTH RP (remove nothere from the email address if mailing direct) "SJC" wrote in message ... Thank you for your help. That formula thread helps quite a bit, but all four formulas depend on if b12 is blank as the formula states, which that would apply only for the first error. How then can I add "if b12 has text" for error 2, 3, and 4? Thanks once again. "Bob Phillips" wrote: =IF(ISBLANK(B12),IF(OR(D12="x",K12="x"),"ERROR 1:Name Missing",IF(COUNTA(D12:K12)<8,"ERROR 2: Data Missing",IF(COUNTIF(D12:K12,"x")1," ERROR 3: More than one column checked",IF(AND(C12="x",H12="x"),"ERROR 4: Wrong data box checked",""))))) I think <g -- HTH RP (remove nothere from the email address if mailing direct) "SJC" wrote in message ... I am trying to create a spreadsheet which others will be using to input data which hopefully will be user friendly. I am wondering if anyone can help me write these formulas--I haven't been able to figure it out. I am trying to create four 'IF' formulas within one cell that will return an error message to the user if they made a mistake on the spreadsheet or do not complete all of the needed cells. When I put in more than one formula into the one cell, I get an error message. Is it possible to enter in more than one formula into one cell, and if so, any ideas on how it should be written? If this is not possible, are there any other ideas on how I could make this work? Thanks in advance. The basics of what I want to say are below. If B12 is blank, and any of D12 through K12 have an 'x', return ERROR 1: Name Missing; If B12 has text, and any of D12 through K12 are blank, return ERROR 2: Data Missing; If B12 has text, and D12 though K12 "x"1, return ERROR 3: More than one column checked; If c12 has "x" and H12 has "x", return ERROR 4: Wrong data box checked. |
#12
|
|||
|
|||
Excellent.
Bob "SJC" wrote in message ... That is perfect!! Thank you so much!! "Bob Phillips" wrote: =IF(COUNTA(B12:K12)=0,"",IF(AND(ISBLANK(B12),OR(D1 2="x",K12="x")),"ERROR 1:Name Missing",IF(COUNTA(D12:K12)<8,"ERROR 2: Data Missing",IF(COUNTIF(D12:K12,"x")1," ERROR 3: More than one column checked",IF(AND(C12="x",H12="x"),"ERROR 4: Wrong data box checked",""))))) -- HTH RP (remove nothere from the email address if mailing direct) "SJC" wrote in message ... Thank you all so much for your help. All of the formulas are working--I appreciate you sharing your expertise. I have only one last question. If all fields are blank, meaning the person did not use all of the lines, there still returns one of the error messages. Is there a way to add to the current formula so that if all fields in the line are blank, no error message will be returned? Thanks once again!! "Bob Phillips" wrote: Maybe this =IF(AND(ISBLANK(B12),OR(D12="x",K12="x")),"ERROR 1:Name Missing",IF(COUNTA(D12:K12)<8,"ERROR 2: Data Missing",IF(COUNTIF(D12:K12,"x")1," ERROR 3: More than one column checked",IF(AND(C12="x",H12="x"),"ERROR 4: Wrong data box checked","")))) -- HTH RP (remove nothere from the email address if mailing direct) "SJC" wrote in message ... Thank you for your help. That formula thread helps quite a bit, but all four formulas depend on if b12 is blank as the formula states, which that would apply only for the first error. How then can I add "if b12 has text" for error 2, 3, and 4? Thanks once again. "Bob Phillips" wrote: =IF(ISBLANK(B12),IF(OR(D12="x",K12="x"),"ERROR 1:Name Missing",IF(COUNTA(D12:K12)<8,"ERROR 2: Data Missing",IF(COUNTIF(D12:K12,"x")1," ERROR 3: More than one column checked",IF(AND(C12="x",H12="x"),"ERROR 4: Wrong data box checked",""))))) I think <g -- HTH RP (remove nothere from the email address if mailing direct) "SJC" wrote in message ... I am trying to create a spreadsheet which others will be using to input data which hopefully will be user friendly. I am wondering if anyone can help me write these formulas--I haven't been able to figure it out. I am trying to create four 'IF' formulas within one cell that will return an error message to the user if they made a mistake on the spreadsheet or do not complete all of the needed cells. When I put in more than one formula into the one cell, I get an error message. Is it possible to enter in more than one formula into one cell, and if so, any ideas on how it should be written? If this is not possible, are there any other ideas on how I could make this work? Thanks in advance. The basics of what I want to say are below. If B12 is blank, and any of D12 through K12 have an 'x', return ERROR 1: Name Missing; If B12 has text, and any of D12 through K12 are blank, return ERROR 2: Data Missing; If B12 has text, and D12 though K12 "x"1, return ERROR 3: More than one column checked; If c12 has "x" and H12 has "x", return ERROR 4: Wrong data box checked. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with named formula's | Excel Worksheet Functions | |||
Formulas | Excel Worksheet Functions | |||
How to make Excel run limited number of formulas on a given worksh | Excel Discussion (Misc queries) | |||
Way to make Excel only run certain formulas on a worksheet? | Excel Discussion (Misc queries) | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions |