I am trying to create individual statements that pull only specified data
from a master sheet based on the data in one particular field (column B
below). Spreadsheet data is as follows:

Mastersheet: Sheet1
A,B,C,D,E
1:Joe;10;Cleveland;OH;196
2:Bill;12;Houston;TX;188
3:Harry;10;Chicago;IL;222
4:Joe;15;SanDiego;CA;311
5:Tom;12;Dallas;TX;124
6:Jim;15;NewYork;NY;297

Move data with the following end result:
Sheet2:
A,B,C,D,E
1:Blank
2:Blank
3: Joe;10;Cleveland;OH;196
4: Harry;10;Chicago;IL;222
5:
6:

Sheet3:
A,B,C,D,E
1:Blank
2:Blank
3: Bill;12;Houston;TX;188
4: Tom;12;Dallas;TX;124
5: Blank
6: Blank

Sheet4:

A,B,C,D,E
1:Blank
2:Blank
3: Joe;15;SanDiego;CA;311
4: Jim;15;NewYork;NY;297
5: Blank
6: Blank

Is there a way to write formulas in Sheets2-4 that pull only the specific
data? If not, is there a way to do this with a macro?

Thanks for your help in advance

"thomaspb" wrote:

I am trying to create individual statements that pull only specified data
from a master sheet based on the data in one particular field (column B
below). Spreadsheet data is as follows:

Mastersheet: Sheet1
A,B,C,D,E
1:Joe;10;Cleveland;OH;196
2:Bill;12;Houston;TX;188
3:Harry;10;Chicago;IL;222
4:Joe;15;SanDiego;CA;311
5:Tom;12;Dallas;TX;124
6:Jim;15;NewYork;NY;297

Move data with the following end result:
Sheet2:
A,B,C,D,E
1:Blank
2:Blank
3: Joe;10;Cleveland;OH;196
4: Harry;10;Chicago;IL;222
5:
6:

Sheet3:
A,B,C,D,E
1:Blank
2:Blank
3: Bill;12;Houston;TX;188
4: Tom;12;Dallas;TX;124
5: Blank
6: Blank

Sheet4:

A,B,C,D,E
1:Blank
2:Blank
3: Joe;15;SanDiego;CA;311
4: Jim;15;NewYork;NY;297
5: Blank
6: Blank

How about something even better, which will also pack it up neatly at the top
in the individual ("child") sheets?

Here's an easy non-array formulas driven model which automates the copying
of lines from a "Parent" data sheet to each "Child" sheet by key col values ..

Illustrated in this sample:
http://freefilehosting.net/download/409k1
Parent to Child AutoCopy via Key Col Values.xls

Construct:
In sheet: WS1 (the "master" / parent sheet)

Assume data in cols A to E, data in row2 down,
with the key col = col B (as per your spec)

List the key col values (col B's unique values) in K1 across,
in **text** format (Just preformat K1 across as TEXT):
10,11,12, etc (the text numbers can be in any order)

Put in K2: =IF($B2="","",IF($B2=K$1+0,ROW(),""))
Copy K2 across & fill down to cover
the max expected extent of source data in the key col B

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.

Then, in a new sheet named: 10 (one of the key col values)
Paste the same col headers into A1:E1 (these are just labels)

Put in A2:
=IF(ROWS($1:1)COUNT(OFFSET(WS1!$J:$J,,MATCH(WSN,W S1!$K$1:$IV$1,0))),"",INDEX(WS1!A:A,MATCH(SMALL(OF FSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1:$IV$1,0)),ROWS( $1:1)),OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$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: 10 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: 11, 12, 13,
etc to get corresponding returns for all "Child" sheets. Adapt to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,300 Files:361 Subscribers:58
xdemechanik
---

WOW!! Worked Great! Just what I was looking for.

THANKS!

"Max" wrote:

How about something even better, which will also pack it up neatly at the top
in the individual ("child") sheets?

Here's an easy non-array formulas driven model which automates the copying
of lines from a "Parent" data sheet to each "Child" sheet by key col values ..

Illustrated in this sample:
http://freefilehosting.net/download/409k1
Parent to Child AutoCopy via Key Col Values.xls

Construct:
In sheet: WS1 (the "master" / parent sheet)

Assume data in cols A to E, data in row2 down,
with the key col = col B (as per your spec)

List the key col values (col B's unique values) in K1 across,
in **text** format (Just preformat K1 across as TEXT):
10,11,12, etc (the text numbers can be in any order)

Put in K2: =IF($B2="","",IF($B2=K$1+0,ROW(),""))
Copy K2 across & fill down to cover
the max expected extent of source data in the key col B

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.

Then, in a new sheet named: 10 (one of the key col values)
Paste the same col headers into A1:E1 (these are just labels)

Put in A2:
=IF(ROWS($1:1)COUNT(OFFSET(WS1!$J:$J,,MATCH(WSN,W S1!$K$1:$IV$1,0))),"",INDEX(WS1!A:A,MATCH(SMALL(OF FSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1:$IV$1,0)),ROWS( $1:1)),OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$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: 10 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: 11, 12, 13,
etc to get corresponding returns for all "Child" sheets. Adapt to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,300 Files:361 Subscribers:58
xdemechanik
---

Welcome. Take a moment to press the "Yes" button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,500 Files:362 Subscribers:59
xdemechanik
---
"thomaspb" wrote:
WOW!! Worked Great! Just what I was looking for.
THANKS!