Plus range of cells from different sheets if not empty
 

Ok, 'path_name\[book_name.xls]sheet_name'!cell_address will return the
contents of the cell - even if it is "0", but I don't want to return the cell
if it is "0", I want excel to check other workbooks and return the value of
the cells -- only if the cell is not "0". Can I somehow run a look to check
one cell, and if that cell is "0", then continue on to check the same cell in
another workbook, and return that value -- if it is not "0"?

"sebastienm" wrote:

Hi,

1. To add values, consider using the SUM() functionto prevent error values.
Say A1 coontains 1 , B1 contains 2, and A2 contains some text (even "")
-in A3, enter the formula: =A1 + B1 + A2 This returns the #Value error.
-now in A3, enter the formula: =SUM( A1:B1,A2) . This returns 3.

2. To return value from external book:
Use the syntax: ='path_name\[book_name.xls]sheet_name'!cell_address
Now if the cell is empty it will return 0.
If the formula returns some text, the SUM() function will still compute
correctly (considering text=0)
Finally, if you really don't want to see any text then do (assuming
returning A1 in sheet1 of c:\book1.xls):
=IF(ISNUMBER('c:\[book1.xls]Sheet1'!A1),'c:\[book1.xls]Sheet1'!A1,"")
This will blank out any non numeric cel.

Regards,
Sebastien

"Help Me Rhonda TOA" wrote:

I have 10 different workbooks and I need to pull three specific cells from
each workbook into a separate workbook only if those specific cells contain
text. How do I create a formula to plus a range of cells only if they are
not null -- given there could be up to 3 cells per workbook, but I only want
to plus them if they are not empty and do nothing if they are empty?