#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 19
Default Summary Sheet Totals

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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 364
Default Summary Sheet Totals

"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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,856
Default Summary Sheet Totals

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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 136
Default Summary Sheet Totals

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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 19
Default Summary Sheet Totals

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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 19
Default Summary Sheet Totals

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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 19
Default Summary Sheet Totals

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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,856
Default Summary Sheet Totals

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Daily Totals on a summary sheet Allewyn Excel Worksheet Functions 10 June 27th 06 05:47 PM
Exclude data from summary totals when hidden XVOTE Excel Worksheet Functions 1 April 13th 06 04:37 PM
totals sheet- need summary of column of names between sheets babs Excel Discussion (Misc queries) 6 February 6th 06 10:35 PM
Summary Sheet Totals gwhite Excel Discussion (Misc queries) 3 July 11th 05 03:28 AM
Exclude hidden data from summary totals XVOTE Excel Discussion (Misc queries) 2 June 3rd 05 06:26 AM


All times are GMT +1. The time now is 06:42 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"