Bring a total from one page to another.

jackel · External Since: Jun 25, 2007 Posts: 1

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 · External Since: Mar 17, 2004 Posts: 1619

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,INDIRECT("'"&$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
>

jackel · External Since: Jun 26, 2007 Posts: 1

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,INDIRECT("'"&$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

Max · External Since: Sep 08, 2003 Posts: 3297

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.

jackel · External Since: Jun 26, 2007 Posts: 1

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

Max · External Since: Sep 08, 2003 Posts: 3297

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" <jackel.c6dfe5 RemoveThis @officefrustration.com> 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.