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

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

Max;2223137 Wrote:
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,




--
jackel

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.

Max;2223394 Wrote:
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.




--
jackel

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.