SUM across named sheets

A

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

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)
 
A

Amer Neely

Andy said:
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)
Click to expand...

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

Jerry

Amer Neely said:
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!"
Click to expand...

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.
 
G

Gord Dibben

Jerry

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


Gord Dibben MS Excel MVP
 
B

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

ISTEXT not working? 6
lookup across multiple sheets & return sum of cells 5
Problem referring to "names" in formulas across sheets 5
Recalc of SUM() that refers to a cell across a range of worksheets 4
Sum A Named Range Across Multiple Columns 3
Indirect Function() - summing across sheets 7
Summing across multiple sheets using a changeable reference cell 2
COUNTIF across sheets 2

Top