Is there a way to populate a summary worksheet with the details of 4 other
worksheets, all of which have the same number of columns (A:J with the labels
on row 5) yet have different numbers of rows (each sheet will have a
different number of rows with the data beginning on row 6 in each sheet).

Is it possible to do without running a macro so that the summary sheet will
look to be automatically populated with as many rows as there are on sheet
number 1, then look to be populated with as many rows as there are on sheet
number 2, etc.

Any help would be much appreciated.
Thanks
Tony

One way which could achieve this ..

Sample implementation at:
http://cjoint.com/?lxhD0otyVR
Auto-Summarizing WorkSheets_wks.xls

In Sheet1
Put in K6:
=IF(A6="","",ROW(A1))
Copy down to say, K15, to cover the max expected data range
(Leave K1:K5 empty)

In Sheet2
Put in K6:
=IF(A6="","",ROW(A1)+MAX(Sheet1!K:K))
Copy down to say, K15, to cover the max expected data range
(Leave K1:K5 empty)

In Sheet3
Put in K6:
=IF(A6="","",ROW(A1)+MAX(Sheet2!K:K))
Copy down to say, K15, to cover the max expected data range
(Leave K1:K5 empty)

In Sheet4
Put in K6:
=IF(A6="","",ROW(A1)+MAX(Sheet3!K:K))
Copy down to say, K15, to cover the max expected data range
(Leave K1:K5 empty)

In a new sheet: Summ
Same labels placed in A5:J5

Put in A6:

=IF(ISERROR(SMALL(Sheet1!$K:$K,ROW(A1))),
IF(ISERROR(SMALL(Sheet2!$K:$K,ROW(A1)-COUNT(Sheet1!$K:$K))),
IF(ISERROR(SMALL(Sheet3!$K:$K,ROW(A1)-
(COUNT(Sheet1!$K:$K)+COUNT(Sheet2!$K:$K)))),
IF(ISERROR(SMALL(Sheet4!$K:$K,ROW(A1)-
(COUNT(Sheet1!$K:$K)+COUNT(Sheet2!$K:$K)+COUNT(She et3!$K:$K)))),"",
INDEX(Sheet4!A:A,MATCH(SMALL(Sheet4!$K:$K,ROW(A1)-
(COUNT(Sheet1!$K:$K)+COUNT(Sheet2!$K:$K)+COUNT(She et3!$K:$K))),Sheet4!$K:$K,
0))),
INDEX(Sheet3!A:A,MATCH(SMALL(Sheet3!$K:$K,ROW(A1)-
(COUNT(Sheet1!$K:$K)+COUNT(Sheet2!$K:$K))),Sheet3! $K:$K,0))),
INDEX(Sheet2!A:A,MATCH(SMALL(Sheet2!$K:$K,ROW(A1)-
COUNT(Sheet1!$K:$K)),Sheet2!$K:$K,0))),
INDEX(Sheet1!A:A,MATCH(SMALL(Sheet1!$K:$K,ROW(A1)) ,Sheet1!$K:$K,0)))

Copy A6 across to J6, fill down to cover the total expected range in the 4
sheets. In this example, the total expected range is: 15 rows per sheet x 4
sheets = 60 rows.

The summary sheet will return exactly what's desired ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Summary Worksheets" <Summary wrote in
message ...
Is there a way to populate a summary worksheet with the details of 4 other
worksheets, all of which have the same number of columns (A:J with the
labels
on row 5) yet have different numbers of rows (each sheet will have a
different number of rows with the data beginning on row 6 in each sheet).

Is it possible to do without running a macro so that the summary sheet
will
look to be automatically populated with as many rows as there are on sheet
number 1, then look to be populated with as many rows as there are on
sheet
number 2, etc.

Any help would be much appreciated.
Thanks
Tony

The criteria set-up in col K's formulae assumes that the "key" col is col A
in each of the 4 sheets: Sheet1, ... Sheet4. If col A is blank (i.e. from
row6 down), then it is assumed that the entire row is blank.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Thank you very much Max, that has worked perfectly. You have helped me
enormously.


"Max" wrote:

The criteria set-up in col K's formulae assumes that the "key" col is col A
in each of the 4 sheets: Sheet1, ... Sheet4. If col A is blank (i.e. from
row6 down), then it is assumed that the entire row is blank.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

You're welcome !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Summary Worksheets" wrote in
message ...
Thank you very much Max, that has worked perfectly. You have helped me
enormously.

Max,
I used your formulas for a spreadsheet I was involved with, and it worked
great, so thank you very much.

I have a quick question however. If there's data in A1 (the key), then
obviously the Summary gets updated; however if I go back and update any of
the sheets 1 thru 4, it seems that the Summary page fails to update. What
might I be doing wrong?

Thanks!
Jim

"Max" wrote:

One way which could achieve this ..

Sample implementation at:
http://cjoint.com/?lxhD0otyVR
Auto-Summarizing WorkSheets_wks.xls

In Sheet1
Put in K6:
=IF(A6="","",ROW(A1))
Copy down to say, K15, to cover the max expected data range
(Leave K1:K5 empty)

In Sheet2
Put in K6:
=IF(A6="","",ROW(A1)+MAX(Sheet1!K:K))
Copy down to say, K15, to cover the max expected data range
(Leave K1:K5 empty)

In Sheet3
Put in K6:
=IF(A6="","",ROW(A1)+MAX(Sheet2!K:K))
Copy down to say, K15, to cover the max expected data range
(Leave K1:K5 empty)

In Sheet4
Put in K6:
=IF(A6="","",ROW(A1)+MAX(Sheet3!K:K))
Copy down to say, K15, to cover the max expected data range
(Leave K1:K5 empty)

In a new sheet: Summ
Same labels placed in A5:J5

Put in A6:

=IF(ISERROR(SMALL(Sheet1!$K:$K,ROW(A1))),
IF(ISERROR(SMALL(Sheet2!$K:$K,ROW(A1)-COUNT(Sheet1!$K:$K))),
IF(ISERROR(SMALL(Sheet3!$K:$K,ROW(A1)-
(COUNT(Sheet1!$K:$K)+COUNT(Sheet2!$K:$K)))),
IF(ISERROR(SMALL(Sheet4!$K:$K,ROW(A1)-
(COUNT(Sheet1!$K:$K)+COUNT(Sheet2!$K:$K)+COUNT(She et3!$K:$K)))),"",
INDEX(Sheet4!A:A,MATCH(SMALL(Sheet4!$K:$K,ROW(A1)-
(COUNT(Sheet1!$K:$K)+COUNT(Sheet2!$K:$K)+COUNT(She et3!$K:$K))),Sheet4!$K:$K,
0))),
INDEX(Sheet3!A:A,MATCH(SMALL(Sheet3!$K:$K,ROW(A1)-
(COUNT(Sheet1!$K:$K)+COUNT(Sheet2!$K:$K))),Sheet3! $K:$K,0))),
INDEX(Sheet2!A:A,MATCH(SMALL(Sheet2!$K:$K,ROW(A1)-
COUNT(Sheet1!$K:$K)),Sheet2!$K:$K,0))),
INDEX(Sheet1!A:A,MATCH(SMALL(Sheet1!$K:$K,ROW(A1)) ,Sheet1!$K:$K,0)))

Copy A6 across to J6, fill down to cover the total expected range in the 4
sheets. In this example, the total expected range is: 15 rows per sheet x 4
sheets = 60 rows.

The summary sheet will return exactly what's desired ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Summary Worksheets" <Summary wrote in
message ...
Is there a way to populate a summary worksheet with the details of 4 other
worksheets, all of which have the same number of columns (A:J with the
labels
on row 5) yet have different numbers of rows (each sheet will have a
different number of rows with the data beginning on row 6 in each sheet).

Is it possible to do without running a macro so that the summary sheet
will
look to be automatically populated with as many rows as there are on sheet
number 1, then look to be populated with as many rows as there are on
sheet
number 2, etc.

Any help would be much appreciated.
Thanks
Tony

"jjjam" wrote:
.. I used your formulas for a spreadsheet
I was involved with, and it worked great,

Glad to hear that !

.. If there's data in A1 (the key), then obviously the
Summary gets updated; however if I go back and
update any of the sheets 1 thru 4,
it seems that the Summary page fails to update.

It should update automatically in the summary sheet with any* updates done
in any of the sheets 1 - 4 (I've just re-tested it here), unless the book's
calc mode is inadvertently set to "Manual"
*not just in the key col A

Try pressing F9 to recalc, does it recalc / work now ?

To check / change calc modes:
Click Tools Options Calculation tab
Check "Automatic" OK

Let me know how it went for you ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

If calc mode is at Auto, then try checking the extents of the formulas
filled in col K in each of the Sheets 1 - 4, and those in the summary sheet
as well. Ensure that it covers the max expected extents of the source data.
In the summary sheet, the formula fill should cover the *total* of the
expected range within the 4 sheets.

Eg, in the sample file's summary sheet,
the formulas are filled down by 60 rows as the expected range is:
15 rows per sheet x 4 sheets = 60 rows

Here's a fresh link to the previous sample:
http://www.savefile.com/files/6151050
Auto-Summarizing WorkSheets_wks.xls
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Auto calc was enabled, but thanks for bringing that to my attention.

Here's what I'm seeing: Assume I have data in sheet1:A1 thus creating an
entry in the Summary sheet. If I go back and clear A1 (using the space bar),
then A1 in the Summary sheet goes blank (which is good), but B1 and C1 of the
Summary remain. It was my understanding that if A1 is blank, then no entry
would be made on the summary, thus I shouldn't see anything in B1 and C1 on
the Summary sheet. Am I correct?

As always, thanks for your help, you're amazing.

... clear A1 (using the space bar),

Aha, that's the culprit <g. Do not use the space bar to clear cells, always
use the Delete key instead.

But just for the record, we could of course, make it more robust against
such "practice" (clearing cells by using the spacebar) by wrapping TRIM()
around the key col A in the formulas in col K in Sheets 1 - 4. TRIM will
remove the "invisible" white spaces. (But it's always better not to use
spacebar to clear cells.)

For example:

Instead of in Sheet1's K6:
=IF(A6="","",ROW(A1))

we could use:
=IF(TRIM(A6)="","",ROW(A1))

then copy down as before

It should work fine now ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"jjjam" wrote in message
...
Auto calc was enabled, but thanks for bringing that to my attention.

Here's what I'm seeing: Assume I have data in sheet1:A1 thus creating an
entry in the Summary sheet. If I go back and clear A1 (using the space
bar),
then A1 in the Summary sheet goes blank (which is good), but B1 and C1 of
the
Summary remain. It was my understanding that if A1 is blank, then no
entry
would be made on the summary, thus I shouldn't see anything in B1 and C1
on
the Summary sheet. Am I correct?

As always, thanks for your help, you're amazing.