formulas from tab to tab

[Guest]

hello, i have a woorbook with tabs for every month and a year end tab. The
tabs have names from cells 5 - 56 on it with performances in A thru K. Is
there a formula i can use for the first individual to have all of their
monthly K5 cells add up on the year to date sheet, and then just drag that
down so it applies to the other 50 people and their specific totals on the
monthly tabs

 

[Guest]

In the year end tab,
assume the 12 monthly sheetnames are listed in A2 down, eg: Jan, Feb, etc
and in B1 across are listed the cell references of interest, eg: K5, M3, etc

Then we could place in B2:
=INDIRECT("'"&$A2&"'!"&B$1)
and copy B2 across and fill down as required to populate the table

Note that the sheetnames listed in A2 down must match exactly what's on the
sheet tabs (except for case), otherwise we'd get #REF! Watch out for any
inconsistencies (eg: extra space, typos, etc)

 

[shail]

Try this one too,

Make 2 sheets. Place the 1st one before January and place the 2nd one
after December tab. Now on the Year sheet write down the formula to sum
the K5 cells

I have named these 2 worksheets as "sheet1" & "sheet2"

=SUM(sheet1:sheet2!K5)

This way the sheets between sheet1 and sheet2 will be sumed. And you
can copy the formula down.

Hope that helps.

Shail

 

[shail]

Oh I guess, there is no need to add two sheets. It can work as this way
too -

=SUM(Janec!K5)

Thanks,

Shail

 

[Gord Dibben]

Shail

Your first post allows for new sheets to be inserted between sheet1 and sheet2
and is a good method.

Best though to name them Start and End so's not to confuse with default sheet
names.


Gord Dibben MS Excel MVP

 

[shail]

Yes Mr Gibbon you are right. Thanks for the tip.

Shail

Your first post allows for new sheets to be inserted between sheet1 and sheet2
and is a good method.

Best though to name them Start and End so's not to confuse with default sheet
names.

Gord Dibben MS Excel MVP



- Show quoted text -

 

[shail]

Yes, Mr Dibben you are right. Thanks for the tip

Shail