As responsed to your multi-post in .misc ..
(please do not multi-post)
---
How to make a summary in a different page based on
dates/activities/Venue/name?
Here's a slightly different take on the above <g> ..
and a play using non-array formulas
which drives out in a sheet: X
an auto-consolidation from the 3 source sheets (stacked)
and then in a sheet: Y,
an auto-sort of X in chronologic sequence by dates
A sample construct is available at:
http://www.savefile.com/files/3089838
Auto-consol n sort data fr multiple sheets by dates.xls
Assume 3 identical structure source sheets named: Bob, Adam, Sheila,
col headers in A1:C1 : Date, Activities, Venue,
data from row2 down to a max expected row10
In Bob,
Put in D2: =IF(A2="","",ROW())
Copy down to D10
In Adam,
Put in D2: =IF(A2="","",ROW()+MAX(Bob!D
))
Copy down to D10
In Sheila,
Put in D2: =IF(A2="","",ROW()+MAX(Adam!D
))
Copy down to D10
In a new sheet X:
Col Headers in A1
1 : Date, Activities, Venue, Name
Put in A2:
=IF(ISERROR(SMALL(Bob!$D:$D,ROW(A1))),
IF(ISERROR(SMALL(Adam!$D:$D,ROW(A1)-COUNT(Bob!$D:$D)))
IF(ISERROR(SMALL(Sheila!$D:$D,ROW(A1)-(COUNT(Bob!$D:$D)+COUNT(Adam!$D:$D)))),""
INDEX(Sheila!A:A,MATCH(SMALL(Sheila!$D:$D,ROW(A1)-(COUNT(Bob!$D:$D)+COUNT(Adam!$D:$D))),Sheila!$D:$D,0)))
INDEX(Adam!A:A,MATCH(SMALL(Adam!$D:$D,ROW(A1)-COUNT(Bob!$D:$D)),Adam!$D:$D,0))),
INDEX(Bob!A:A,MATCH(SMALL(Bob!$D:$D,ROW(A1)),Bob!$D:$D,0)))
Copy A2 to C2
Put in D2
=IF(ROW(A1)-1<COUNT(Bob!D
),"Bob",IF(ROW(A1)-1<COUNT(Adam!D
)+COUNT(Bob!D
),"Adam",IF(ROW(A1)-1<COUNT(Sheila!D
)+COUNT(Adam!D
)+COUNT(Bob!D
),"Sheila","")))
Select A2
2, copy down to D30
X gathers and returns the results from the 3 source sheets, in this sequence:
Lines from Bob, then those from Adam, then those from Sheila (stacked)
In a new sheet Y:
Col Headers in A1
1 : Date, Activities, Venue, Name
Put in A2:
=IF(ISERROR(SMALL($E:$E,ROW(A1))),"",
INDEX(X!A:A,MATCH(SMALL($E:$E,ROW(A1)),$E:$E,0)))
Copy A2 to D2
Put in E2:
=IF(X!A2="","",X!A2+ROW()/10^10)
Select A2:E2, copy down to E30
Y returns the results from the 3 source sheets,
sorted in chronologic sequence by the dates in col A
---
