SUM across named sheets

[Amer Neely]

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.

--
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!"

 

[Peo Sjoblom]

Try

=SUM(Monday:Sunday!B7:B45)

Regards,

Peo Sjoblom

 

[Amer Neely]

Try

=SUM(Monday:Sunday!B7:B45)

Regards,

Peo Sjoblom

Excellent. Right on the button. Many thanks.

--
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!"

 

[Amer Neely]

Try

=SUM(Monday:Sunday!B7:B45)

Regards,

Peo Sjoblom

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

Shouldn't that work?
--
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!"

 

[Bob Phillips]

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

--
HTH

Bob Phillips

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

 

[Amer Neely]

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

Shouldn't that work?

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

works. Again, thank you.

--
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!"

 

[Andy Wiggins]

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)

 

[Amer Neely]

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

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

--
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!"

 

[Amer Neely]

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)

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

--
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!"

 

[Gord Dibben]

Jerry

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


Gord Dibben MS Excel MVP

 

[Bob Phillips]

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

Bob