Here's a formulas driven model which delivers the required automation ..
Illustrated in this sample:
http://freefilehosting.net/download/40eb5
Parent to Child AutoCopy Model_KeyCol D.xls
In sheet: WS1 (the "master"/"parent" sheet)
Data in cols A to E, from row2 down,
with the key col = col D (as per spec)
List the key col values (col D's unique values) in M1 across, eg:
Hostel1, Hostel2, etc (list can be in any order, but must
match exactly with what's on the tabs, except for case)
Put in M2: =IF($D2="","",IF($D2=M$1,ROW(),""))
Copy M2 across & fill down to cover
the max expected extent of source data in the key col D
Click Insert > Name > Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")
))+1,32)
Click OK
The above defines WSN as a name
we can use to refer to the sheetname in formulas
It will auto-extract the sheetname implicitly
Technique came from a post by Harlan
In a new sheet named: Hostel1
With the same col headers pasted into A1:E1
Put in A2:
=IF(ROWS($1:1)>COUNT(OFFSET(WS1!$L:$L,,MATCH(WSN,WS1!$M$1:$IV$1,0))),"",INDEX(WS1!A:A,MATCH(SMALL(OFFSET(WS1!$L:$L,,MATCH(WSN,WS1!$M$1:$IV$1,0)),ROWS($1:1)),OFFSET(WS1!$L:$L,,MATCH(WSN,WS1!$M$1:$IV$1,0)),0)))
Copy A2 across to E2, fill down to say, E10
(copy down by the smallest possible range sufficient
to cover the max expected extent for any key col value.
Here, I've assumed that 9 rows (rows 2 to 10) is sufficient)
Cols A to E will return only the lines
for the key col value: Hostel1 from "WS1",
with all lines neatly packed at the top
Dress this sheet up nicely to taste, then just make copies of it, rename as
the other key col values: Hostel2, Hostel3, etc to get corresponding returns.
Adapt to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,600 Files:362 Subscribers:60
xdemechanik
