Allewyn

I told you an untruth.

Excel's SUM ignores non-numerics so you should not be getting the error even
if a cell on a sheet is non-numeric.

You must have a #value! error in one of the cells.

Caused by what is hard to tell from here.

Assuming you are summing A1 on each sheet, what is in A1?

Entered number? Number returned by formula?

If you want to send me a copy of the workbook, I will have a look.

Change the AT and DOT in my email address to @ and .


Gord Dibben Excel MVP

On Thu, 22 Dec 2005 08:32:03 -0800, "Allewyn"
wrote:

OK, thanks for the clarification. I have one last question: if the cells on
each sheet are the same and have numberical values, is there a reason I might
get the #value! error message? I've checked each cell to make sure the
formatting is "general", including the cell I'm calling from. Should I use
=sum(value(a1)) then CONTROL SHIFT ENTER to verify for this on each sheet?

I appreciate your help and wish you the merriest of Christmases!

"Gord Dibben" wrote:

Allewyn

The sheets between Start and End can be named any way you want as long as it's
a legal name.

Instead of the word "cellref" you are to enter a specific cell address like A1
or E45

The values in any of those cells must be numeric or you will get the error.

This method is useful only when summing same cell on each sheet.

If cells to sum are spread about, you need to point to them cell by cell and
sheet by sheet in your SUM formula.


Gord

On Wed, 21 Dec 2005 14:35:07 -0800, "Allewyn"
wrote:

Does it matter if the sheets between Start and End are named other than
sheet2, sheet3, etc? Mine are and I get the dreaded #VALUE? uisng the
=sum(Start:End!A1) method. Thought I understood. Am I supposed to include
the characters "cellref" in there?

The other thing is, if this does work (which I'm sure it will when I get it
right) how does it address the need for daily results getting displayed in
different cells on my summary page?

"Gord Dibben" wrote:

Allewyn

If the total cell on each sheet is same cell you can use

=SUM(Sheet1:Sheet23!cellref)

To allow for inserting more sheets later, I recommend inserting a dummy sheet
named Start at first sheet in book.

Insert a dummy sheet named End at last sheet in book.

Then formula can be =SUM(Start:End!cellref)

New sheets will be inserted between Start and End sheets.


Gord Dibben Excel MVP

On Wed, 21 Dec 2005 09:11:03 -0800, "Allewyn"
wrote:

I have 14 sheets in the workbook
Each sheet has names and numbers and a ‘total calls made’ cell which I call
from a separate summary sheet
I am tracking calls everyday on the 14 different sheets (different categories)
Yesterday I went through the pain of typing =sum(, click sheet tab, select
cell, +, click next sheet tab, select cell, +, etc.
Yesterday I made 16 calls and the above approach worked great
Today I made 3 calls
Now it doesn’t work as great because I want to keep the daily totals
separate on the summary sheet without having to do what I did yesterday.

Column
J2
Row A2 16 Calls yesterday display in A2
3 Calls today
currently 19 displayed on summary sheet from yesterday in
row A2 again, wiping out the prior
number 16
would like to display 3 on summary sheet in a different cell from
the 16 prior calls, such as A4, without having
to go through typing (+, clicking
sheet tab, select cell), (+ clicking
sheet tab, select cell), etc every day.

Is there a way to do this with an automatic update from each sheet to the
summary sheet?