Bring a total from one page to another.

Hello,

I have a work book with allot of pages and want the total amounts from the various pages to self add on a single page for quick viewing instead of going to that actual page and scrolling to the very bottom to see the total.

The totals page has each department name and under that name I want the totals to be real time auto add on page 1 under that name.

If I can get one example I will be able to do the rest.

Page 2 name is CTS and the total line is on "G, 112" the total is 118,000

I want the total on page 1 under CTS to look like this;


CTS - 118,000
CGS - 52,500


Thanks for the help

One way using INDEX/MATCH with INDIRECT ..

Assume identically structured source sheets titled as: CTS, CGS, etc
where col G in each sheet contains the required totals fig
and where there is a unique* text-phrase, say: Total
which is in the cell just to the left of the required totals fig
(eg: if the totals fig is in G112, the text-phrase is in F112)
*unique in col F, that is

Then in your summary sheet,
Enter the unique text-phrase in B1: Total
List the source sheetnames in A2 down, viz: CTS, CGS ...

Put in B2:
=INDEX(INDIRECT("'"&$A2&"'!G:G"),MATCH(B$1,INDIREC T("'"&$A2&"'!F:F"),0))
Copy B2 down to return the required results.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"jackel" wrote:

Hello,

I have a work book with allot of pages and want the total amounts from
the various pages to self add on a single page for quick viewing
instead of going to that actual page and scrolling to the very bottom
to see the total.

The totals page has each department name and under that name I want the
totals to be real time auto add on page 1 under that name.

If I can get one example I will be able to do the rest.

Page 2 name is CTS and the total line is on "G, 112" the total is
118,000

I want the total on page 1 under CTS to look like this;


CTS - 118,000
CGS - 52,500


Thanks for the help




--
jackel

Originally Posted by Max

One way using INDEX/MATCH with INDIRECT ..

Assume identically structured source sheets titled as: CTS, CGS, etc
where col G in each sheet contains the required totals fig
and where there is a unique* text-phrase, say: Total
which is in the cell just to the left of the required totals fig
(eg: if the totals fig is in G112, the text-phrase is in F112)
*unique in col F, that is

Then in your summary sheet,
Enter the unique text-phrase in B1: Total
List the source sheetnames in A2 down, viz: CTS, CGS ...

Put in B2:
=INDEX(INDIRECT("'"&$A2&"'!G:G"),MATCH(B$1,INDIREC T("'"&$A2&"'!F:F"),0))
Copy B2 down to return the required results.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"jackel" wrote:

Hello,

I have a work book with allot of pages and want the total amounts from
the various pages to self add on a single page for quick viewing
instead of going to that actual page and scrolling to the very bottom
to see the total.

The totals page has each department name and under that name I want the
totals to be real time auto add on page 1 under that name.

If I can get one example I will be able to do the rest.

Page 2 name is CTS and the total line is on "G, 112" the total is
118,000

I want the total on page 1 under CTS to look like this;


CTS - 118,000
CGS - 52,500


Thanks for the help




--
jackel

I copied the formula like you instructed and it didn't work, if you give me an email address I can send the workbook to you to insert the formula in one block so I can see how you did it, then I can copy to the other cells.

Thanks,

Here's a quick sample to illustrate the earlier set-up in working order:
http://cjoint.com/?gAlYn6EumA
Jackel_links.xls
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"jackel" wrote
I copied the formula like you instructed and it didn't work, if you
give me an email address I can send the workbook to you to insert the
formula in one block so I can see how you did it, then I can copy to
the other cells.

Originally Posted by Max

Here's a quick sample to illustrate the earlier set-up in working order:
http://cjoint.com/?gAlYn6EumA
Jackel_links.xls
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"jackel" wrote
I copied the formula like you instructed and it didn't work, if you
give me an email address I can send the workbook to you to insert the
formula in one block so I can see how you did it, then I can copy to
the other cells.

The book you sent works perfectly but will not work the way it needs to work. I have 3 months on one page named April, May, June so any of the months can be printed at will for review. At the top of each month is our company logo and I beleive that is the reason why it doesn't work. There are breaks so the calculation isn't going to happen.

I'll figure something out, thanks for the assistance.

welcome. you might consider posting your new queries in
..excel.worksheet.functions, which is a more active group with lots of
responders. cheers.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"jackel" wrote
The book you sent works perfectly but will not work the way it needs to
work. I have 3 months on one page named April, May, June so any of the
months can be printed at will for review. At the top of each month is
our company logo and I beleive that is the reason why it doesn't work.
There are breaks so the calculation isn't going to happen.

I'll figure something out, thanks for the assistance.