| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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!!! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!!! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!!! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Autofill summary worksheet
|
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!!! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!!! |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!!! |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copying worksheet cells into another worksheet using autofill | Excel Discussion (Misc queries) | |||
| Trying to list tab/worksheet names in a summary worksheet | Excel Discussion (Misc queries) | |||
| Summary worksheet reference to detail worksheet | New Users to Excel | |||
| summary data sheet from worksheet to worksheet | Excel Worksheet Functions | |||
| Link worksheet totals to a summary worksheet in the same workbook | Excel Worksheet Functions | |||
Linear Mode
