I have a file with 4 columns in sheet1. Name-Town-Job-Type. There are 4
possible entries for "Town". If the town entry in sheet one is "X", I want
that entire row to be copied to sheet2, If"Y" to sheet 3. If "z" to sheet4.
If "ZZ" to sheet 5.
Can this be done?
Any help appreciated
--
John

One way ..

In Sheet1
------------
Assume the sample table below is in cols A to D
data from row2 down
(with the key column "Town" in col B), viz:

Name Town Job Type
Name1 X Job1 Type1
Name2 Y Job2 Type2
Name3 Z Job3 Type3
Name4 ZZ Job4 Type4
Name5 X Job5 Type5
Name6 Y Job6 Type6
Name7 Z Job7 Type7
Name8 ZZ Job8 Type8
etc

List across in say, F1:I1
the 4 Towns: X, Y, Z, ZZ

Put in F2: =IF($B2="","",IF($B2=F$1,ROW(),""))

Copy F2 across to I2, then fill down by a safe "max"
number of rows that data is ever expected in cols A to D,
say, down to I1000?

In a new sheet named: X
---------------------------------------
Let's reserve cell A1 for the "Town" name

Put in A1:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32)

(This'll extract the sheetname: X into A1.
But you need to save the file first)

Copy Paste the same col headers from Sheet1
into A2:D2, i.e.: Name, Town, Job, Type

Put in A3:

=IF(ISERROR(SMALL(OFFSET(Sheet1!$E:$E,0,MATCH($A$1 ,Sheet1!$F$1:$I$1,0)),ROWS
($A$1:A1))),"",INDEX(Sheet1!A:A,MATCH(SMALL(OFFSET (Sheet1!$E:$E,0,MATCH($A$1
,Sheet1!$F$1:$I$1,0)),ROWS($A$1:A1)),OFFSET(Sheet1 !$E:$E,0,MATCH($A$1,Sheet1
!$F$1:$I$1,0)),0)))

Note: You'd need to correct / restore the couple of inevitable line wraps /
line breaks when you copy paste the above formula into A3

Copy A3 across to D3, fill down by as many rows as was done in Sheet1, viz.
down to D1000 thereabouts

You'll see that cols A to D (in row3 down)
will auto-return the "filtered" rows for the Town: X from Sheet1,
i.e. for the sample data-set above, it'll appear as:

Name1 X Job1 Type1
Name5 X Job5 Type5
(rest are blank rows)

Now, just duplicate / make a copy of the sheet: X,
rename it as: Y
and you'll get the "filtered" rows for Town: Y

Name2 Y Job2 Type2
Name6 Y Job6 Type6
(rest are blank rows)

Repeat the sheet duplication / renaming
for the remaining 2 sheets: Z and ZZ

Data entered into Sheet1 will auto-appear
in each of the Towns' sheet: X, Y, Z, ZZ

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"John" wrote in message
...
I have a file with 4 columns in sheet1. Name-Town-Job-Type. There are 4
possible entries for "Town". If the town entry in sheet one is "X", I
want
that entire row to be copied to sheet2, If"Y" to sheet 3. If "z" to
sheet4.
If "ZZ" to sheet 5.
Can this be done?
Any help appreciated
--
John

I would probably use a Pivot table; one for every sheet.
Then just select each town.

Ola

Details:
Put you 4 column as Row Items, and any column as Data field.
Deselect any subtotals and grand totals.
Mind the lenght of your pivot table and press ! every time the basetable
change.