Autofill summary worksheet

[Cheryl]

Hi,

I have a workbook that has a worksheet for each month of the year. Each
month has over a dozen columns with figures that are totalled. Then I have a
summary worksheet in the same workbook that has a column for each month of
the year and all of the individual columns on the month sheets. I need to
figure out how to put a formula in the summary worksheet that comes from each
of the column totals on each of the monthly sheets without entering each
manually.

On the summary sheet I manually entered just the column total formula for
the month of January. Now I'm assuming that there is some way to drag those
cells across the rest of the year and get it to copy and adjust the forumula
respectively. But I can't find what the keystrokes or process is to get it
to work...

Here's what the forumulas look like in the january columns:

=sum(Jan!Y40)
=sum(Jan!A40)
=sum(Jan!B40)
=sum(Jan!C40) etc....

Here's what I want this sheet to look like by columns:

=sum(Jan!Y40) =sum(Feb!Y40) =sum(Mar!Y40)
=sum(Jan!A40) =sum(Feb!A40) =sum(Mar!A40)
=sum(Jan!B40) =sum(Feb!B40) =sum(Mar!B40)
=sum(Jan!C40) =sum(Feb!C40) =sum(Mar!C40)

When I drag the cells this is what I get:

=sum(Jan!Y40) =sum(Jan!Z40) =sum(Jan!AA40)
=sum(Jan!A40) =sum(Jan!B40) =sum(Jan!C40)
=sum(Jan!B40) =sum(Jan!C40) =sum(Jan!D40)
=sum(Jan!C40) =sum(Jan!D40) =sum(Jan!E40)

So it's changing the column ID instead of the sheet ID. Any ideas on how to
make this work?

Thanks!!!

 

[Shane Devenshire]

Hi,

This all depends on whether the rows correspond on all the sheets. If so,
suppose the Totals are in column P on each sheet, on your summary sheet enter
a formula like

=SUM(Janec!P2)
copy this formula down and you will be getting the totals for each line for
each sheet.
With this kind of formula sheet position is important - the first sheet
should be Jan the last Dec and no other sheets should be between them.

If this helps, please click Yes.

Cheers,
Shane Devenshire

 

[Cheryl]

What that formula is doing is totalling the P2 column for all the sheets.
What I need is it to transfer the value in each P2 column individually.

 

[Don Guillett]

Set up a vlookup table
c d
1 jan
2 Feb
etc
=INDIRECT(VLOOKUP(COLUMN(B1),$C:$D,2)&"!y40")
drag across

 

[Don Guillett]

Do NOT use the sum part of your formula UNLESS you do want to SUM a range of
cells. Use as shown

 

[Ashish Mathur]

Hi,

You may also look up question 30 on my site -
http://ashishmathur.com/replies.aspx

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Cheryl]

I've not setup or used a vlookup table before. Is there somewhere that
really basic instructions are located? Thanks!

 

[Ashish Mathur]

Help menu

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Don Guillett]

As Ashish said, look in the help index for VLOOKUP