Combine or Merge Issue

[James Monroe]

Greetings:

First, I have machines with both Office 2000 and 2003, so I have both
available if that matters.

Now, the issue:

Our company accountant generates a sheet every two weeks with 401(k)
figures...what the employee contributed, what the employer
contributed, etc.

Each sheet is exactly the same as far as what data goes where, but the
amounts sometimes change from pay period to pay period.

So, at the end of the year, she has 26 sheets. Is there a way to
create a 27th sheet and combine all the data from the first 26?

For example, if in A3 we have a dollar amount that represents what
Employee X contributed, can I somehow add all the A3's together and
have the total of all of them appear in A3 on that 27th sheet?

Of course, I mean to automate this somehow. I realize I could copy and
paste with a + after each C&P, but that would take forever and a day.

Thanks in advance for any help.

James

 

[Chip Pearson]

James,

Try a formula like

=SUM(Sheet1:Sheet26!A3)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[James Monroe]

James,

Try a formula like

=SUM(Sheet1:Sheet26!A3)

Well, yeah, I know that would work, but I was trying to avoid having
to type formulas for the next month or so..

The formula you wrote above would have to be duplicated 26 times
(Sheet2:Sheet25!A3) (...24!Ac) etc for EACH CELL.

I thought that perhaps there was a function where I could put all 26
sheets in the same folder and say "Combine all the A3's from all the
sheets."

See what I mean?

Thanks for your reply.

 

[Peo Sjoblom]

No it does not have to be duplicated, Chip's formula will total all A3
in sheets 1, 2, 3, 4, 5 and so on. And if you are smart you would use 2
dummy sheets
that always will be the first and the last sheet, that way you don't have to
change the formula

=SUM(First:Last!A3)

just make sure when you add a new sheet it will be between the first and the
last

 

[James Monroe]

No it does not have to be duplicated, Chip's formula will total all A3
in sheets 1, 2, 3, 4, 5 and so on. And if you are smart you would use 2
dummy sheets
that always will be the first and the last sheet, that way you don't have to
change the formula

=SUM(First:Last!A3)

just make sure when you add a new sheet it will be between the first and the
last

Ah....I see now.

Thanks very much for the detailed explanation; I misunderstood Chip's
meaning.

Looking back at it now, I see where he was correct and I missed it.

Thanks to both of you.

James