http://www.savefile.com/files/6862951
Auto summarize n stack lines from 12 primary sheets.zip
Note: To facilitate data input, the calc mode in the sample is intentionally
set to manual mode. Just press F9 to recalc/update the summary upon
completion / as required.
The 12 source sheets (identically structured) has data assumed in cols A to
E, headers in A5:E5, data from row6 to a max expected row130 (125 lines). Key
col checked is col A (whether empty or not), viz.: if col A is empty, it's
construed that the entire row is empty.
In sheet: Summ
Same labels placed in A5:E5
Using 12 empty cols to the right ..
List the 12 sheetnames in R5:AC5 in the stacking sequence* that the contents
are to be summarized
*viz: lines from sheetname in R5, followed by lines from S5, then lines from
T5, and so on (flexibility provided here). Ensure that the sheetnames in
M5:P5 match exactly with the names on the sheet tabs (except for case). Watch
out for any inconsistencies (extra spaces, typos, etc)
In R6, copied down to R130*:
=IF(INDIRECT("'"&R$5&"'!A"&ROW(A1)+5)="","",ROW(A1 ))
*assuming max expected data in any sheet is 125 lines, from row6 down
In S6, copied across to AC6, filled down to AC130:
=IF(INDIRECT("'"&S$5&"'!A"&ROW(A1)+5)="","",ROW(A1 )+MAX(R$6:R$15))
Create the following defined ranges (via Insert Name Define):
_1 =Summ!$R$6:$R$130
_2 =Summ!$S$6:$S$130
_3 =Summ!$T$6:$T$130
_4 =Summ!$U$6:$U$130
_5 =Summ!$V$6:$V$130
_6 =Summ!$W$6:$W$130
_7 =Summ!$X$6:$X$130
_8 =Summ!$Y$6:$Y$130
_9 =Summ!$Z$6:$Z$130
_10 =Summ!$AA$6:$AA$130
_11 =Summ!$AB$6:$AB$130
_12 =Summ!$AC$6:$AC$130
R_1 =Summ!$R$6:$AC$130
R_2 =Summ!$R$6:$AB$130
R_3 =Summ!$R$6:$AA$130
R_4 =Summ!$R$6:$Z$130
R_5 =Summ!$R$6:$Y$130
R_6 =Summ!$R$6:$X$130
R_7 =Summ!$R$6:$W$130
R_8 =Summ!$R$6:$V$130
R_9 =Summ!$R$6:$U$130
R_10 =Summ!$R$6:$T$130
R_11 =Summ!$R$6:$S$130
In A6, copied across to E6, filled down to E1505:
=IF(ROW(A1)COUNT(_1),IF(ROW(A1)COUNT(R_11),IF(RO W(A1)COUNT(R_10),IF(ROW(A1)COUNT(R_9),G6,
INDEX(OFFSET(INDIRECT("'"&$U$5&"'!A:A"),,COLUMN(A1 )-1),MATCH(SMALL(_4,ROW(A1)-(COUNT(R_10))),_4,0)+5)),
INDEX(OFFSET(INDIRECT("'"&$T$5&"'!A:A"),,COLUMN(A1 )-1),MATCH(SMALL(_3,ROW(A1)-(COUNT(R_11))),_3,0)+5)),
INDEX(OFFSET(INDIRECT("'"&$S$5&"'!A:A"),,COLUMN(A1 )-1),MATCH(SMALL(_2,ROW(A1)-COUNT(_1)),_2,0)+5)),
INDEX(OFFSET(INDIRECT("'"&$R$5&"'!A:A"),,COLUMN(A1 )-1),MATCH(SMALL(_1,ROW(A1)),_1,0)+5))
In G6, copied across to K6, filled down to K1505:
=IF(ROW(A1)COUNT(R_8),IF(ROW(A1)COUNT(R_7),IF(RO W(A1)COUNT(R_6),IF(ROW(A1)COUNT(R_5),L6,
INDEX(OFFSET(INDIRECT("'"&$Y$5&"'!A:A"),,COLUMN(A1 )-1),MATCH(SMALL(_8,ROW(A1)-(COUNT(R_6))),_8,0)+5)),
INDEX(OFFSET(INDIRECT("'"&$X$5&"'!A:A"),,COLUMN(A1 )-1),MATCH(SMALL(_7,ROW(A1)-(COUNT(R_7))),_7,0)+5)),
INDEX(OFFSET(INDIRECT("'"&$W$5&"'!A:A"),,COLUMN(A1 )-1),MATCH(SMALL(_6,ROW(A1)-(COUNT(R_8))),_6,0)+5)),
INDEX(OFFSET(INDIRECT("'"&$V$5&"'!A:A"),,COLUMN(A1 )-1),MATCH(SMALL(_5,ROW(A1)-(COUNT(R_9))),_5,0)+5))
In L6, copied across to P6, filled down to P1505:
=IF(ROW(A1)COUNT(R_4),IF(ROW(A1)COUNT(R_3),IF(RO W(A1)COUNT(R_2),IF(ROW(A1)COUNT(R_1),"",
INDEX(OFFSET(INDIRECT("'"&$AC$5&"'!A:A"),,COLUMN(A 1)-1),MATCH(SMALL(_12,ROW(A1)-COUNT(R_2)),_12,0)+5)),
INDEX(OFFSET(INDIRECT("'"&$AB$5&"'!A:A"),,COLUMN(A 1)-1),MATCH(SMALL(_11,ROW(A1)-(COUNT(R_3))),_11,0)+5)),
INDEX(OFFSET(INDIRECT("'"&$AA$5&"'!A:A"),,COLUMN(A 1)-1),MATCH(SMALL(_10,ROW(A1)-(COUNT(R_4))),_10,0)+5)),
INDEX(OFFSET(INDIRECT("'"&$Z$5&"'!A:A"),,COLUMN(A1 )-1),MATCH(SMALL(_9,ROW(A1)-(COUNT(R_5))),_9,0)+5))
The above formula fills will cover the the aggregated/total expected range
in the 12 sheets. (max 125 lines/rows expected per sheet x 12 sheets = 1500
rows)
Cols A to E will return the desired summary, ie auto-stacked lines from cols
A to E in the 12 source sheets where the key col A is not empty. Lines will
be neatly stacked at the top in the sequence of the sheetnames listed in
R5:AC5.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---