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

James,

Try a formula like

=SUM(Sheet1:Sheet26!A3)


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


"James Monroe" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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

On Sat, 15 Nov 2003 05:17:35 -0600, "Chip Pearson" <(E-Mail Removed)>
wrote:

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

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

--

Regards,

Peo Sjoblom

"James Monroe" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Sat, 15 Nov 2003 05:17:35 -0600, "Chip Pearson" <(E-Mail Removed)>
> wrote:
>
> >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.
>

On Sat, 15 Nov 2003 13:36:21 -0500, "Peo Sjoblom" <(E-Mail Removed)>
wrote:

>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