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
---