Hi lanos,
Give this a try.
Firstly define a dynamic range for each sheet's column you need to copy. Assuming there are NO blank cells in the range. Here is a tutorial about defining dynamic range.
Consider these are the named ranges. Name1, Name2, Name3, Name4, Name5 (total 5 sheets)
Then in summary insert two rows above row 1, then in
A1:0
B1: =COUNTA(Name1)
C1: =COUNTA(Name2)
D1: =COUNTA(Name3)
E1: =COUNTA(Name4)
F1: =COUNTA(Name5)
A2: =SUM($A1:A1) then copy across to F2
Then in A5, and copy down as needed.
=IFERROR(INDEX(CHOOSE(MATCH(ROWS(A$5:A5)-1,A$2:F$2),Name1,Name2,Name3,Name4,Name5),ROWS(A$5:A5)-LOOKUP(ROWS(A$5:A5)-1,A$2:F$2)),"")
Bookmarks