Sum

[Sum problem]

I would like to sum a common cell, by defined name, accross a set of
different sheet. The cell name is the same, with a scope to the sheet only.
Example: sheet1!total + sheet2!total + sheet3!total... and so on
I want to be able to do this without having to type in the above, but have
been unable to determine how to get something like the following to work:
Example: =sum(sheet1!:sheet2!,total)
I would be most grateful if anyone can show me how to do this. Thanks.

 

[Bob Bridges]

Whew! Someone else may know another way - are lots of things you can do with
Excel that I've never heard of yet - but this sounds to me like a call for
you to write your own worksheet function in VBA, if you're willing to take
that on. It's not that hard, but if you've never done any programming it
does require learning a new skill. Or do you already know how?

 

[Sum problem]

For clarification on this problem let me add that the cell location is not
the same on each sheet, but the name is the same. Although I would enjoy the
challenge I don't have the time to pick up the necessary skills to program
this - I'm hoping that there is a way to put a formula together that will
simply sum all cells with the defined name "Total" from each sheet.

 

[T. Valko]

The cell name is the same, with a scope to the sheet only.

How have you defined the sheet level names? Like this: !$A$1

Well, when you refer to a sheet level name that is the same across many
sheets it only applies to the name that corresponds to the active sheet.
AFAIK there is no way to do this.

What's wrong with this:

=SUM(Sheet1:Sheet10!A1)

 

[Sum problem]

Since the data is not always the same, the cell location with a sum total
will not be the same, thus the sheet level name for that value "Total", so
sheet1!:sheet2!a1 will not work. Thanks.

 

[Sum problem]

Attached book example?

Is the attached book example what you are after ?

Corey....

 

[Corey]

There is a Sample Attached to the post.
Paper Clip Icon

 

[T. Valko]

The OP is using the web interface so they won't see or get any attachments.

 

[Corey]

I can email you the exapmle if you like ?

Attached book example?

 

[Spiky]

Can you even reference sheet-specific names in another sheet at all?

Maybe you should duplicate all these sums in another cell of each
sheet, same cell on each sheet. Say, at the top of the column instead
of at the bottom. Then you can use a simple 3D reference, no named
range necessary. Make the Text Color white if you don't want to see
it.

Or, if the data is at least structured the same, make each Sheet long
enough to cover your needs and put the totals at the bottom of the
range in the same cell. You can hide rows to avoid printing/scrolling
extra rows.

Otherwise, if it is fewer than 30 sheets, I think you'll just have to
enter each cell or name as an argument in your SUM.