Autofill summary worksheet
 

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

Autofill summary worksheet
 

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(Jan:Dec!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" wrote:

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

Autofill summary worksheet
 

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.

"Shane Devenshire" wrote:

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(Jan:Dec!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" wrote:

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

Autofill summary worksheet
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Cheryl" wrote in message
...
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!!!

Autofill summary worksheet
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Cheryl" wrote in message
...
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!!!

Autofill summary worksheet
 

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" wrote in message
...
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!!!

Autofill summary worksheet
 

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

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Cheryl" wrote in message
...
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!!!

Autofill summary worksheet
 

Help menu

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

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

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Cheryl" wrote in message
...
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!!!

Autofill summary worksheet
 

As Ashish said, look in the help index for VLOOKUP

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

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

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Cheryl" wrote in message
...
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!!!