Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I looked and can not find a similar question, so I apologize if I'm posting
one that has been asked and answered. I have a workbook with multiple worksheets. I need a summary sheet that collects the totals for the managers to veiw. Each worksheets contains 2 tables, each with a total. The users add rows as needed to each tables, which makes the "Totals" cell move. How do I create a summary sheet that can capture the 2 totals field of each worksheet and have one total in one column and the second total in another column, along with the worksheet name, as in the following example: Col A Col B Col C Sheet1 $35 $0 Any help would be great! Thank you! |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
"Bowtie63" wrote in message
... I looked and can not find a similar question, so I apologize if I'm posting one that has been asked and answered. I have a workbook with multiple worksheets. I need a summary sheet that collects the totals for the managers to veiw. Each worksheets contains 2 tables, each with a total. The users add rows as needed to each tables, which makes the "Totals" cell move. How do I create a summary sheet that can capture the 2 totals field of each worksheet and have one total in one column and the second total in another column, along with the worksheet name, as in the following example: Col A Col B Col C Sheet1 $35 $0 Any help would be great! Thank you! Just create simple formulas. For example, if the $35 total is in cell A20 of Sheet2, in the Col B cell where you want the total to appear, use the formula =Sheet2!A20 (Similarly in Col C for the $0 total.) As users add rows, the totals cells will move, but the references in these formulas will adjust accordingly. |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
You can insert a new row at the very top of your worksheets and link
two cells, eg A1 and B1 to the total cells in the tables below. That way, if rows are inserted in the tables these cells will always show the totals. You can hide these new rows so the sheets look exactly the same as they do now. Then in your summary sheet you will know that the totals are always in A1 and B1 of the subsidiary sheets, so you can set up your formulae quite easily: =Sheet1!A1 and =Sheet1!B1 and so on for the other sheets. If you want to pick up the sheet name from column A of your master sheet, then you could have in column B: =INDIRECT("'"&A1&"'!A$1) and =INDIRECT("'"&A1&"'!B$1) in column C, and then copy down. Hope this helps. Pete On Oct 29, 3:37 pm, Bowtie63 wrote: I looked and can not find a similar question, so I apologize if I'm posting one that has been asked and answered. I have a workbook with multiple worksheets. I need a summary sheet that collects the totals for the managers to veiw. Each worksheets contains 2 tables, each with a total. The users add rows as needed to each tables, which makes the "Totals" cell move. How do I create a summary sheet that can capture the 2 totals field of each worksheet and have one total in one column and the second total in another column, along with the worksheet name, as in the following example: Col A Col B Col C Sheet1 $35 $0 Any help would be great! Thank you! |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hi,
In you summary sheet.. You have your sheet names in column A For Sheet 1 totals In column B on your summary sheet type = and click on your total from Sheet 1 Column B, do the same for Column C. Repeat the steps above for the other sheets. Hope this helps. Regards, Gav. "Bowtie63" wrote: I looked and can not find a similar question, so I apologize if I'm posting one that has been asked and answered. I have a workbook with multiple worksheets. I need a summary sheet that collects the totals for the managers to veiw. Each worksheets contains 2 tables, each with a total. The users add rows as needed to each tables, which makes the "Totals" cell move. How do I create a summary sheet that can capture the 2 totals field of each worksheet and have one total in one column and the second total in another column, along with the worksheet name, as in the following example: Col A Col B Col C Sheet1 $35 $0 Any help would be great! Thank you! |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Thank you, Pete_UK, it was the method I needed. Thanks!
"Pete_UK" wrote: You can insert a new row at the very top of your worksheets and link two cells, eg A1 and B1 to the total cells in the tables below. That way, if rows are inserted in the tables these cells will always show the totals. You can hide these new rows so the sheets look exactly the same as they do now. Then in your summary sheet you will know that the totals are always in A1 and B1 of the subsidiary sheets, so you can set up your formulae quite easily: =Sheet1!A1 and =Sheet1!B1 and so on for the other sheets. If you want to pick up the sheet name from column A of your master sheet, then you could have in column B: =INDIRECT("'"&A1&"'!A$1) and =INDIRECT("'"&A1&"'!B$1) in column C, and then copy down. Hope this helps. Pete On Oct 29, 3:37 pm, Bowtie63 wrote: I looked and can not find a similar question, so I apologize if I'm posting one that has been asked and answered. I have a workbook with multiple worksheets. I need a summary sheet that collects the totals for the managers to veiw. Each worksheets contains 2 tables, each with a total. The users add rows as needed to each tables, which makes the "Totals" cell move. How do I create a summary sheet that can capture the 2 totals field of each worksheet and have one total in one column and the second total in another column, along with the worksheet name, as in the following example: Col A Col B Col C Sheet1 $35 $0 Any help would be great! Thank you! |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hi Stephen,
Thank you! It works! "Stephen" wrote: "Bowtie63" wrote in message ... I looked and can not find a similar question, so I apologize if I'm posting one that has been asked and answered. I have a workbook with multiple worksheets. I need a summary sheet that collects the totals for the managers to veiw. Each worksheets contains 2 tables, each with a total. The users add rows as needed to each tables, which makes the "Totals" cell move. How do I create a summary sheet that can capture the 2 totals field of each worksheet and have one total in one column and the second total in another column, along with the worksheet name, as in the following example: Col A Col B Col C Sheet1 $35 $0 Any help would be great! Thank you! Just create simple formulas. For example, if the $35 total is in cell A20 of Sheet2, in the Col B cell where you want the total to appear, use the formula =Sheet2!A20 (Similarly in Col C for the $0 total.) As users add rows, the totals cells will move, but the references in these formulas will adjust accordingly. |
#7
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Thank you, Gav123!
"Gav123" wrote: Hi, In you summary sheet.. You have your sheet names in column A For Sheet 1 totals In column B on your summary sheet type = and click on your total from Sheet 1 Column B, do the same for Column C. Repeat the steps above for the other sheets. Hope this helps. Regards, Gav. "Bowtie63" wrote: I looked and can not find a similar question, so I apologize if I'm posting one that has been asked and answered. I have a workbook with multiple worksheets. I need a summary sheet that collects the totals for the managers to veiw. Each worksheets contains 2 tables, each with a total. The users add rows as needed to each tables, which makes the "Totals" cell move. How do I create a summary sheet that can capture the 2 totals field of each worksheet and have one total in one column and the second total in another column, along with the worksheet name, as in the following example: Col A Col B Col C Sheet1 $35 $0 Any help would be great! Thank you! |
#8
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Glad it worked - thanks for feeding back.
Pete On Oct 29, 6:17 pm, Bowtie63 wrote: Thank you, Pete_UK, it was the method I needed. Thanks! "Pete_UK" wrote: You can insert a new row at the very top of your worksheets and link two cells, eg A1 and B1 to the total cells in the tables below. That way, if rows are inserted in the tables these cells will always show the totals. You can hide these new rows so the sheets look exactly the same as they do now. Then in your summary sheet you will know that the totals are always in A1 and B1 of the subsidiary sheets, so you can set up your formulae quite easily: =Sheet1!A1 and =Sheet1!B1 and so on for the other sheets. If you want to pick up the sheet name from column A of your master sheet, then you could have in column B: =INDIRECT("'"&A1&"'!A$1) and =INDIRECT("'"&A1&"'!B$1) in column C, and then copy down. Hope this helps. Pete On Oct 29, 3:37 pm, Bowtie63 wrote: I looked and can not find a similar question, so I apologize if I'm posting one that has been asked and answered. I have a workbook with multiple worksheets. I need a summary sheet that collects the totals for the managers to veiw. Each worksheets contains 2 tables, each with a total. The users add rows as needed to each tables, which makes the "Totals" cell move. How do I create a summary sheet that can capture the 2 totals field of each worksheet and have one total in one column and the second total in another column, along with the worksheet name, as in the following example: Col A Col B Col C Sheet1 $35 $0 Any help would be great! Thank you!- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Daily Totals on a summary sheet | Excel Worksheet Functions | |||
Exclude data from summary totals when hidden | Excel Worksheet Functions | |||
totals sheet- need summary of column of names between sheets | Excel Discussion (Misc queries) | |||
Summary Sheet Totals | Excel Discussion (Misc queries) | |||
Exclude hidden data from summary totals | Excel Discussion (Misc queries) |