MrExcel Publishing
Your One Stop for Excel Tips & Solutions

trouble with formulas


Posted by Niki on January 25, 2002 7:00 AM

I am using a formula that copies multiple cells from a different workbook plus adds text. The problem is when there is nothing in the cell that I copied, the text still shows. Is there a way to keep the cell blank, if the cells I copied are blank?


Posted by Aladin Akyurek on January 25, 2002 7:04 AM

Care to post the formula that you use?

Posted by Niki on January 25, 2002 7:11 AM

=[Laura.xls]Jan!$B$5&"AL "&[Laura.xls]Jan!$C$5"SL"

Posted by Aladin Akyurek on January 25, 2002 7:21 AM

=IF(AND(LEN([Laura.xls]Jan!$B$5),LEN([Laura.xls]Jan!$C$5)),[Laura.xls]Jan!$B$5&"AL "&[Laura.xls]Jan!$C$5"SL","")

This formula does concat if both B5 and C5 in Laura are NOT empty and reurns a blank otherwise.

Posted by Niki on January 25, 2002 10:46 AM

The formula is not doing what I want it to. I now get a blank cell, but even when I enter data in B5 or C5 Laura, I still get a blank cell! What am I doing wrong?

Posted by Aladin Akyurek on January 25, 2002 10:58 AM

Niki --

=IF(AND(LEN([Laura.xls]Jan!$B$5),LEN([Laura.xls]Jan!$C$5)),[Laura.xls]Jan!$B$5&"AL "&[Laura.xls]Jan!$C$5&"SL","")

One & was missing. But this should work.

Aladin

=========

Posted by Niki on January 25, 2002 11:11 AM


Something still isn't working right. I get a blank cell even when I put data in a cell I am coping from.


Posted by Niki on January 25, 2002 11:17 AM

I see what is happening!! With this formula I have to have data in both cells for it to show up. (you told me that earlier, it just took me a while to understand.) Is there a way for it to work if I only have data in one of the cells? By the way, THANK YOU for all the help!!!!


Posted by Aladin Akyurek on January 25, 2002 11:32 AM

Gee. That's great. The long and the short answer to the new question is yes:

=TRIM(IF(LEN([Laura.xls]Jan!$B$5),[Laura.xls]Jan!$B$5&"AL","")&IF(LEN([Laura.xls]Jan!$C$5)), " "&[Laura.xls]Jan!$C$5&"SL",""))

Watch out when copying this. You should not miss the space before the last Laura bit.

Aladin

========