SUM across named sheets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Amer Neely - 08 Jan 2007 23:49 GMT

I'm trying to get a sum of cell ranges from (named) sheets.

For example I have sheets named for the week days:
Sunday Monday Tuesday Wednesday Thursday Friday Saturday

In each sheet I have employees and hours.

I would like to do a SUM of all hours worked for an employee in all the
sheets.

In OpenOffice I can reference a cell in a different sheet: Sunday.A1

My question, how do I refer to a cell in a different (named sheet) in
Excel?

I'm thinking something like
=SUM(Monday.B7:B45,Tuesday.b7:b45,Wednesday.b7:b45,Thursday.b7:b45,Friday.b7:b45,Saturday.b7:b45,Sunday.b7:b45)
would work but I'm getting a 'NAME#' error in the cell.

Signature

Amer Neely
w: www.softouch.on.ca/
b: www.softouch.on.ca/blog/
Perl | MySQL programming for all data entry forms.
"We make web sites work!"

Reply to this Message

Peo Sjoblom - 08 Jan 2007 23:54 GMT

Try

=SUM(Monday:Sunday!B7:B45)

Regards,

Peo Sjoblom

> I'm trying to get a sum of cell ranges from (named) sheets.
>
[quoted text clipped - 15 lines]
>
> would work but I'm getting a 'NAME#' error in the cell.

Reply to this Message

Amer Neely - 09 Jan 2007 00:07 GMT

> Try
>
[quoted text clipped - 23 lines]
>>
>> would work but I'm getting a 'NAME#' error in the cell.

Excellent. Right on the button. Many thanks.

Signature

Amer Neely
w: www.softouch.on.ca/
b: www.softouch.on.ca/blog/
Perl | MySQL programming for all data entry forms.
"We make web sites work!"

Reply to this Message

Amer Neely - 09 Jan 2007 00:13 GMT

> Try
>
[quoted text clipped - 23 lines]
>>
>> would work but I'm getting a 'NAME#' error in the cell.

Hmmm. Not quite. That works for the 2 days, but using the same format
for all 7 days breaks.

Shouldn't that work?

Signature

Amer Neely
w: www.softouch.on.ca/
b: www.softouch.on.ca/blog/
Perl | MySQL programming for all data entry forms.
"We make web sites work!"

Reply to this Message

Bob Phillips - 09 Jan 2007 00:21 GMT

Are the sheets in strict day order, not Monday, Sunday, Tuesday, ... for
example.

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> > Try
> >
[quoted text clipped - 20 lines]
> >>
> >> I'm thinking something like

=SUM(Monday.B7:B45,Tuesday.b7:b45,Wednesday.b7:b45,Thursday.b7:b45,Friday.b7

:b45,Saturday.b7:b45,Sunday.b7:b45)
> >>
[quoted text clipped - 4 lines]
>
> Shouldn't that work?

Reply to this Message

Amer Neely - 09 Jan 2007 01:03 GMT

> Are the sheets in strict day order, not Monday, Sunday, Tuesday, ... for
> example.

Yes, I found it easier to order them that way.

Signature

Amer Neely
w: www.softouch.on.ca/
b: www.softouch.on.ca/blog/
Perl | MySQL programming for all data entry forms.
"We make web sites work!"

Reply to this Message

Amer Neely - 09 Jan 2007 00:22 GMT

>> Try
>>
[quoted text clipped - 28 lines]
>
> Shouldn't that work?

Got it. I was making it too difficult:
=SUM(Sunday:Saturday!B7:B45)

works. Again, thank you.

Signature

Amer Neely
w: www.softouch.on.ca/
b: www.softouch.on.ca/blog/
Perl | MySQL programming for all data entry forms.
"We make web sites work!"

Reply to this Message

Andy Wiggins - 09 Jan 2007 00:25 GMT

This file might be a help:
http://www.bygsoftware.com/examples/zipfiles/consol.zip
It's in the "Accountants" section on page:
http://www.bygsoftware.com/examples/examples.htm

The "Bread-Roll" consolidation method - great for accountants. See how
simple it is to consolidate any combination of your organisation's
accounts. (No VBA used)

>> Try
>>
[quoted text clipped - 28 lines]
>
> Shouldn't that work?

Signature

Regards
Andy
www.BygSoftware.com

Reply to this Message

Amer Neely - 09 Jan 2007 01:05 GMT

> This file might be a help:
> http://www.bygsoftware.com/examples/zipfiles/consol.zip
[quoted text clipped - 37 lines]
>>
>> Shouldn't that work?

Thanks, I'll take a look at them all.

Signature

Amer Neely
w: www.softouch.on.ca/
b: www.softouch.on.ca/blog/
Perl | MySQL programming for all data entry forms.
"We make web sites work!"

Reply to this Message

Jerry - 09 Jan 2007 03:02 GMT

> I'm trying to get a sum of cell ranges from (named) sheets.
>
[quoted text clipped - 14 lines]
> =SUM(Monday.B7:B45,Tuesday.b7:b45,Wednesday.b7:b45,Thursday.b7:b45,Friday.b7:b45,Saturday.b7:b45,Sunday.b7:b45)
> would work but I'm getting a 'NAME#' error in the cell.

If I understand what you are trying to do, here is a solution.

=SUM('Monday'!B7:B45,'Tuesday'!b7:b45,'Wednesday'!b7:b45,'Thursday'!b7:b45,'Friday'!b7:b45,'Saturday'!b7:b45,'Sunday'!b7:b45)The named sheets must be enclosed in single quotes followed by anexclamation mark (!). This is the only way it will work.To prove it, in the cell you wish to contain the summary of hours type"=Sum(" then click on the sheet and then the cell you want to summarize.When you have clicked on the cell to be summarized, type a comma (,) thenselect the next sheet and cell as above. You do this for each cell you wantto total. When you are finished type a closed paren ) and press enter. youwill see the summed data in the cell.Hope this helps.

Reply to this Message

Gord Dibben - 09 Jan 2007 04:23 GMT

Jerry

The single quotes 'January' are only necessary if the sheet name contains a
space.

Gord Dibben MS Excel MVP

>> I'm trying to get a sum of cell ranges from (named) sheets.
>>
[quoted text clipped - 18 lines]
>
> =SUM('Monday'!B7:B45,'Tuesday'!b7:b45,'Wednesday'!b7:b45,'Thursday'!b7:b45,'Friday'!b7:b45,'Saturday'!b7:b45,'Sunday'!b7:b45)The named sheets must be enclosed in single quotes followed by anexclamation mark (!). This is the only way it will work.To prove it, in the cell you wish to contain the summary of hours type"=Sum(" then click on the sheet and then the cell you want to summarize.When you have clicked on the cell to be summarized, type a comma (,) thenselect the next sheet and cell as above. You do this for each cell you wantto total. When you are finished type a closed paren ) and press enter. youwill see the summed data in the cell.Hope this helps.

Reply to this Message

Bob Phillips - 09 Jan 2007 09:58 GMT

It is better practice to always use IMO, then you never forget.
Unfortunately, Excel helpfully removes them when there are no spaces!

Bob

> Jerry
>
> The single quotes 'January' are only necessary if the sheet name contains a
> space.

Reply to this Message

Rate this thread: