Help with isblank

[bademployee]

Hi all

=E$2&E$55&" " & F$2&F$55&" "& G$2&G$55&" "& H$2&H$55&" "& I$2&I$55&" "& J$2&J$55&" "& K$2&K$55&" "& L$2&L$55&" "& M$2&M$55&" "& N$2&N$55&" "& O$2&O$55&" "

I use this formula to return both the values in row55 and row2, all in one cell.

Problem is, I don't want the value in row2 to display if row 55 is empty.

Any help would be greatly appreciated.

Mark

 

[p45cal]

I s'pose somethinhg laborious like:

=IF($E$55="","",E$2&E$55&" " ) & IF($F$55="","",F$2&F$55&" ")& IF($G$55="","",G$2&G$55&" ")& IF($H$55="","",H$2&H$55&" ")& IF($I$55="","",I$2&I$55&" ")& IF($J$55="","",J$2&J$55&" ")& IF($K$55="","",K$2&K$55&" ")& IF($L$55="","",L$2&L$55&" ")& IF($M$55="","",M$2&M$55&" ")& IF($N$55="","",N$2&N$55&" ")& IF($O$55="","",O$2&O$55&" ")

 

[bademployee]

Thanks

Works well;

=IF($E$57=0,"",E$2&E$57&" ")&IF($F$57=0,"",F$2&F$57&" ")&IF($G$57=0,"",G$2&G$57&" ")&IF($H$57=0,"",H$2&H$57&" ")&IF($I$57=0,"",I$2&I$57&" ")&IF($J$57=0,"",J$2&J$57&" ")&IF($K$57=0,"",K$2&K$57&" ")&IF($L$57=0,"",L$2&L$57&" ")&IF($M$57=0,"",M$2&M$57&" ")&IF($N$57=0,"",N$2&N$57&" ")&IF($O$57=0,"",O$2&O$57&" ")

The only issue now is the value returned has 13 decimal places, where as I only want 2.

Each cell in row 57 has formatting: [=0]"";##.00

Any idea have I can do this?

Mark

 

[Rick Rothstein]

Since you are concatenating text together, I'm guessing your formula is returning text, not a number, so number formatting has no effect. See if changing your formula slight to this makes a difference...

=1*(IF($E$57=0,"",E$2&E$57&" ")&IF($F$57=0,"",F$2&F$57&" ")&IF($G$57=0,"",G$2&G$57&" ")&IF($H$57=0,"",H$2&H$57&" ")&IF($I$57=0,"",I$2&I$57&" ")&IF($J$57=0,"",J$2&J$57&" ")&IF($K$57=0,"",K$2&K$57&" ")&IF($L$57=0,"",L$2&L$57&" ")&IF($M$57=0,"",M$2&M$57&" ")&IF($N$57=0,"",N$2&N$57&" ")&IF($O$57=0,"",O$2&O$57&" "))

All I have done is put "1*(" in front of the first IF (without the quote marks, of course) and a closeing parenthesis at the end of the formula.

 

[bademployee]

Doesn't seem to work...

Mark

 

[Rick Rothstein]

Doesn't seem to work...

I haven't actually tried your formula out (I'm lazy and don't want to set everyting up)... I responded based on your statement "The only issue now is the value returned has 13 decimal places, where as I only want 2." I see some spaces being concatenated in your formula, so I am wondering what your output actually looks like. Can you post a same of the actual output your formula is generating? Change your formula to this so you can tell me if there are any leading or trailing blank spaces...

="<"&IF($E$57=0,"",E$2&E$57&" ")&IF($F$57=0,"",F$2&F$57&" ")&IF($G$57=0,"",G$2&G$57&" ")&IF($H$57=0,"",H$2&H$57&" ")&IF($I$57=0,"",I$2&I$57&" ")&IF($J$57=0,"",J$2&J$57&" ")&IF($K$57=0,"",K$2&K$57&" ")&IF($L$57=0,"",L$2&L$57&" ")&IF($M$57=0,"",M$2&M$57&" ")&IF($N$57=0,"",N$2&N$57&" ")&IF($O$57=0,"",O$2&O$57&" ")&">"

Copy/Paste Special/Values to another cell and then copy/paste that value into your response (do that instead of merely typing what you think you see).

 

[bademployee]

Don't know if I'm doing it correctly;

"<>" when there is no value in row 57 or

"<N15.7171310154271 P11.402555836979 S12.0263642643334 Cu0.161846649781257 Zn0.269744416302095 >" when there is.

The bold is what is in row 2, the numbers are from row 57.

Thanks in advance

Mark

 

[Rick Rothstein]

Ah, you have several numbers all in one cell. You will have to apply the TEXT function to each part of your formula that is returning a number. Using your first IF function call as an example...

Change this: IF($E$57=0,"",E$2&E$57&" ")&

To this: IF($E$57=0,"",E$2&TEXT(E$57,"0.00")&" ")

You will have to make a similar change to all the other IF function calls in your formula.

 

[bademployee]

Perfect!

Just what I was looking for,

Many thanks Rick