Summary Sheet Totals

[Guest]

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!

 

[Stephen]

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.

 

[Pete_UK]

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

 

[Guest]

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.

 

[Guest]

Thank you, Pete_UK, it was the method I needed. Thanks!

 

[Guest]

Hi Stephen,
Thank you! It works!

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.

 

[Guest]

Thank you, Gav123!

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.

 

[Pete_UK]

Glad it worked - thanks for feeding back.

Pete

Thank you, Pete_UK, it was the method I needed. Thanks!








- Show quoted text -