MrExcel Publishing
Your One Stop for Excel Tips & Solutions

#VALUE! When adding empty cells on different worksheets


Posted by Chuck on October 17, 2001 3:01 PM

I am having a problem adding cells that are on separate pages of a workbook in the same cell address for each page in a workbook. I am also getting the #VALUE! error. Is there a simple example of how to add all these cells and avoid this error.


Posted by Qroozn on October 17, 2001 3:16 PM

?

What is your current formula in the cell?
have you changed the name of a worksheet?


Posted by Aladin Akyurek on October 17, 2001 10:04 PM

In order to add together in Sheet1, say,

Sheet2!C4
Sheet3!C4
Sheet4!C4

use

=SUM(Sheet2:Sheet4!C4)

Aladin

Posted by Chuck on October 22, 2001 10:55 AM

re

The worksheets name changes from month to month!

These are Week Ending dates. I need to calculate total production for a month. I have been trying to simplify this spreadsheet and hit this snag. When I have a group that has had no production over the last few weeks all those blanks add up to #VALUE! . I want to get rid of this and leave the cell empty when there is no total.

Posted by Aladin Akyurek on October 22, 2001 1:55 PM

#VALUE! error

Chuck,

Adding blank (that is, empty) cells should result in zero, not in #VALUE!.

My guess is that you might have text in (one of) those cells, e.g., a space (that is, " ") or a formula that returns a blank ("").

Try the following formula to see whether this is true:

=IF(LEN('WE 10-7-01'!I8)>0,'WE 10-7-01'!I8,0)+IF(LEN('WE 10-14-01'!I8)>0,'WE 10-14-01'!I8,0)+IF(LEN('WE 10-21-01'!I8)>0,WE 10-21-01'!I8 ,0)+IF(LEN('WE 10-28-01'!I8)>0,'WE 10-28-01'!I8,0)+IF(LEN(BLANK!I8)>0,BLANK!I8,0)

If this comes out right, I'd suggest to change the formula in I8 everywhere to return 0 instead of "" or " ".

Aladin

================

Posted by Chuck on October 31, 2001 11:18 AM

Re: #VALUE! error


Got it Thanks!

Chuck