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
========
