Excel GURUs help. (How to make program parse more than 1 forumla within a cell)

[Idz21]

Alright, so what I'm trying to do is have Excel append multiple custom
Error Text in one cell, if there are more than 1 errors.

So here's an example. I want all this code parsed within one cell.
We'll call it A10

Code:
--------------------

=IF(AE1="OK","","ERROR 1")
=IF(AE3="OK","","ERROR 2")
=IF(AE5="OK","","ERROR 3")

--------------------


So, if a single row has three errors (not equal to OK), then I want the
cell to report (all within 1 cell):
-ERROR 1
ERROR 2
ERROR 3-

Are there line breaks that I can use in Excel in order to achieve this?
Help!

 

[Harlan Grove]

Idz21 wrote...
....

So here's an example. I want all this code parsed within one cell.
We'll call it A10

=IF(AE1="OK","","ERROR 1")
=IF(AE3="OK","","ERROR 2")
=IF(AE5="OK","","ERROR 3")

So, if a single row has three errors (not equal to OK), then I want the
cell to report (all within 1 cell):
-ERROR 1
ERROR 2
ERROR 3-

Are there line breaks that I can use in Excel in order to achieve this?

A10:
="-"&MID(IF(AE1="OK","",CHAR(10)&"ERROR 1")
&IF(AE3="OK","",CHAR(10)&"ERROR 2")
&IF(AE5="OK","",CHAR(10)&"ERROR 3"),2,256)&"-"

 

[Idz21]

Idz21 wrote...
....
this?

A10:
="-"&MID(IF(AE1="OK","",CHAR(10)&"ERROR 1")
&IF(AE3="OK","",CHAR(10)&"ERROR 2")
&IF(AE5="OK","",CHAR(10)&"ERROR 3"),2,256)&"-"

Would I be able to do this for 10 different columns? (Not sure if
Excel has limitation. I know it only allows 7 nested IF statements.
But I'm not sure what you did is nesting. Just wanted to throw the
question out there, while I try your code.

Thanks!

 

[Idz21]

Here's what I did

Code:
--------------------

="-"&MID(IF(AE23="OK","",CHAR(50)&"ERROR 1")
&IF(AB23="OK","",CHAR(50)&"ERROR 2")
&IF(Y23="OK","",CHAR(50)&"ERROR 3")
&IF(V23="OK","",CHAR(50)&"ERROR 4")
&IF(S23="OK","",CHAR(50)&"ERROR 5")
&IF(P23="OK","",CHAR(50)&"ERROR 6")
&IF(M23="OK","",CHAR(50)&"ERROR 7")
&IF(J23="OK","",CHAR(50)&"ERROR 8")
&IF(G23="OK","",CHAR(50)&"ERROR 9")
&IF(D23="OK","",CHAR(50)&"ERROR 10"),2,256)&"-"

--------------------


However, this is what my output looks like: -ERROR 1*2*ERROR 2*2*ERROR
3*2*ERROR 4*2*ERROR 5-

The bald 2s, are separating the output, however the spacing is
difficult to read. Is there a line break code in Excel?

 

[Pete]

Yes, you want CHAR(10) not CHAR(50) - just do Find & Replace (CTRL-H)
to change them over.

Hope this helps,

Pete

 

[Idz21]

Alright, I got it figured out. Had to use CHAR(10), and format sell to
Wrap Text.


Thanks a lot for your help!