Sum Workbook on Worksheet

I would like to create a summary worksheet at the end of my workbook that
would add all the Acres (see below) of each Lessor on all worksheets. Each
sheet of the workbook contains information like below. Each Lessor is on one
or more of the app. 100 sheets, as are Lessees.
Lessor Acreage Interest Acres Royalty Lessee
Joe 100 0.25 25 0.125 Humble
Sam 200 0.75 150 0.125 Gulf
Edd 80 1.00 80 0.125 Gulf
Mac 640 0.50 320 0.125 Humble
I would like to make a summary sheet that be similar to the other
worksheets, but with totals of all worksheets of the workbook by each Lessor
and/or by each Lessee.
Thanks
Jerry

One possibility is illustrated in this sample construct:
http://www.savefile.com/files/609942
Summarizing multishts by key col n col header.xls

In the summary sheet,

Lessor Table
Create a DV droplist in C2 to allow selection of col header of
relevance/interest, eg; Acres, Acreage
List the source sheetnames in C3:E3, eg: X, Y
List the lessors in B4 down

Core extract formula
In C4:
=IF(OR($B4="",C$3=""),"",SUMIF(INDIRECT("'"&C$3&"' !A:A"),$B4,OFFSET(INDIRECT("'"&C$3&"'!A:A"),,MATCH ($C$2,INDIRECT("'"&C$3&"'!1:1"),0)-1)))
Copy C4 to E4, fill down to populate.
(A "Totals" col can be placed at the end)

Similar construct can be made for the Lessee table
(illustrated below the Lessor table in the sample)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Scafidel" wrote:
I would like to create a summary worksheet at the end of my workbook that
would add all the Acres (see below) of each Lessor on all worksheets. Each
sheet of the workbook contains information like below. Each Lessor is on one
or more of the app. 100 sheets, as are Lessees.
Lessor Acreage Interest Acres Royalty Lessee
Joe 100 0.25 25 0.125 Humble
Sam 200 0.75 150 0.125 Gulf
Edd 80 1.00 80 0.125 Gulf
Mac 640 0.50 320 0.125 Humble
I would like to make a summary sheet that be similar to the other
worksheets, but with totals of all worksheets of the workbook by each Lessor
and/or by each Lessee.
Thanks
Jerry

Max,
Thanks for your detailed response. I believe this will work, but some of
the formula are new to me and I'm having trouble applying them. On my sheet,
the first 9 rows are various parts of a heading and that seems to cause a
problem. Also, short of hiding columns, is there a way to combine all sheets
(X,Y,Z) for Joe's acreage? Thanks
Jerry

"Max" wrote:

One possibility is illustrated in this sample construct:
http://www.savefile.com/files/609942
Summarizing multishts by key col n col header.xls

In the summary sheet,

Lessor Table
Create a DV droplist in C2 to allow selection of col header of
relevance/interest, eg; Acres, Acreage
List the source sheetnames in C3:E3, eg: X, Y
List the lessors in B4 down

Core extract formula
In C4:
=IF(OR($B4="",C$3=""),"",SUMIF(INDIRECT("'"&C$3&"' !A:A"),$B4,OFFSET(INDIRECT("'"&C$3&"'!A:A"),,MATCH ($C$2,INDIRECT("'"&C$3&"'!1:1"),0)-1)))
Copy C4 to E4, fill down to populate.
(A "Totals" col can be placed at the end)

Similar construct can be made for the Lessee table
(illustrated below the Lessor table in the sample)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Scafidel" wrote:
I would like to create a summary worksheet at the end of my workbook that
would add all the Acres (see below) of each Lessor on all worksheets. Each
sheet of the workbook contains information like below. Each Lessor is on one
or more of the app. 100 sheets, as are Lessees.
Lessor Acreage Interest Acres Royalty Lessee
Joe 100 0.25 25 0.125 Humble
Sam 200 0.75 150 0.125 Gulf
Edd 80 1.00 80 0.125 Gulf
Mac 640 0.50 320 0.125 Humble
I would like to make a summary sheet that be similar to the other
worksheets, but with totals of all worksheets of the workbook by each Lessor
and/or by each Lessee.
Thanks
Jerry

"Scafidel" wrote:
Max,
Thanks for your detailed response. I believe this will work, but some of
the formula are new to me and I'm having trouble applying them. On my sheet,
the first 9 rows are various parts of a heading and that seems to cause a
problem.

In the expression:
SUMIF(INDIRECT("'"&C$3&"'!A:A"),$B4,
OFFSET(INDIRECT("'"&C$3&"'!A:A"),,
MATCH($C$2,INDIRECT("'"&C$3&"'!1:1"),0)-1)))

The col headings for "Acreage", "Acres" are presumed to be in row1 within
the individual source sheets. The correct col to grab within row1 is done via
the col param in the OFFSET, viz the part: ...
MATCH($C$2,INDIRECT("'"&C$3&"'!1:1"),0)-1

So if your col headers are actually in say, row 8, try adjusting it to:
MATCH($C$2,INDIRECT("'"&C$3&"'!8:8"),0)-1

Also, short of hiding columns, is there a way to combine all sheets
(X,Y,Z) for Joe's acreage?

I'd always prefer the earlier approach where it's easy n clear to see what's
happening. We flesh out the individual returns for "Joe" for "Acreage" from
all the source sheets, then just have a simple totals col to derive it. For
easy visibility w/o the need to hide cols, we could always place the totals
col at the left, just to the right of the lessor names col, and then have the
individual sheet cols splashed to the right of it.

But if you really want to drive out just the totals by col header by lessor,
from all source sheets at one go, suggest you put in a new post for insights
from others. Don't think it's impossible, but it's going to get very complex
...
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

VoilÃ*! Thanks, Max. That did the trick! You're right, it will look better
with the individual returns. This will really look nice, neat and simple.
Jerry



"Max" wrote:

"Scafidel" wrote:
Max,
Thanks for your detailed response. I believe this will work, but some of
the formula are new to me and I'm having trouble applying them. On my sheet,
the first 9 rows are various parts of a heading and that seems to cause a
problem.

In the expression:
SUMIF(INDIRECT("'"&C$3&"'!A:A"),$B4,
OFFSET(INDIRECT("'"&C$3&"'!A:A"),,
MATCH($C$2,INDIRECT("'"&C$3&"'!1:1"),0)-1)))

The col headings for "Acreage", "Acres" are presumed to be in row1 within
the individual source sheets. The correct col to grab within row1 is done via
the col param in the OFFSET, viz the part: ...
MATCH($C$2,INDIRECT("'"&C$3&"'!1:1"),0)-1

So if your col headers are actually in say, row 8, try adjusting it to:
MATCH($C$2,INDIRECT("'"&C$3&"'!8:8"),0)-1

Also, short of hiding columns, is there a way to combine all sheets
(X,Y,Z) for Joe's acreage?

I'd always prefer the earlier approach where it's easy n clear to see what's
happening. We flesh out the individual returns for "Joe" for "Acreage" from
all the source sheets, then just have a simple totals col to derive it. For
easy visibility w/o the need to hide cols, we could always place the totals
col at the left, just to the right of the lessor names col, and then have the
individual sheet cols splashed to the right of it.

But if you really want to drive out just the totals by col header by lessor,
from all source sheets at one go, suggest you put in a new post for insights
from others. Don't think it's impossible, but it's going to get very complex
..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Glad to hear that. You're welcome.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Scafidel" wrote in message
...
Voilà! Thanks, Max. That did the trick! You're right, it will look
better
with the individual returns. This will really look nice, neat and simple.
Jerry