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.

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" wrote:
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.

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.

"Bob Bridges" wrote:

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" wrote:
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.

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)

--
Biff
Microsoft Excel MVP


"Sum problem" <Sum wrote in message
...
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.

Is the attached book example what you are after ?

Corey....

"Sum problem" wrote in message
...
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.

"Bob Bridges" wrote:

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" wrote:
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.

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.

"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"Sum problem" <Sum wrote in message
...
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.

Attached book example?

"Corey" wrote:

Is the attached book example what you are after ?

Corey....

"Sum problem" wrote in message
...
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.

"Bob Bridges" wrote:

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" wrote:
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

There is a Sample Attached to the post.
Paper Clip Icon
"Sum problem" wrote in message
...
Attached book example?

"Corey" wrote:

Is the attached book example what you are after ?

Corey....

"Sum problem" wrote in message
...
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.

"Bob Bridges" wrote:

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" wrote:
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

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

--
Biff
Microsoft Excel MVP


"Corey" wrote in message
...
There is a Sample Attached to the post.
Paper Clip Icon
"Sum problem" wrote in message
...
Attached book example?

"Corey" wrote:

Is the attached book example what you are after ?

Corey....

"Sum problem" wrote in message
...
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.

"Bob Bridges" wrote:

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" wrote:
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

I can email you the exapmle if you like ?

"Sum problem" wrote in message
...
Attached book example?

"Corey" wrote:

Is the attached book example what you are after ?

Corey....

"Sum problem" wrote in message
...
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.

"Bob Bridges" wrote:

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" wrote:
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

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.