Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

#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.

Check out our Excel Resources

?

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?



Re: #VALUE! When adding empty cells on different worksheets

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


re

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

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.


#VALUE! error

Posted by Aladin Akyurek on October 22, 2001 1:55 PM
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

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


Re: #VALUE! error

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


Got it Thanks!

Chuck


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.