Adapting from a recent post by Bob Phillips
here's one play to automate it using array formulas ..
Sample construct at:
http://www.savefile.com/files/7715209
AutoFiltering_Data_To_Resp_Sheet_ArrayFormula_DefinedName.xls
Assume the master list is in sheet: Master
in cols A to D, headers in row1, data from row2 down
(Key ID is in col A, the tech #)
TECH ACCT# STAT ADDRESS
603 162395-7 CP 6844 N DE CHELLY
607 164655-11 CP 10700 N LA RESERVE LOOP # 2106
603 267454-1 CP 7270 S SAND DUNE DR D
609 131976-3 CP 5702 N CAM LAGUNA VLY
etc
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 Grove.)
In a new sheet named: 603
Paste the col headers over into A1
1
Put in A2, array-enter the formula (press CTRL+SHIFT+ENTER):
=IF(ROW()-ROW(A$2:A$100)+1>ROWS(Master!$A$2:$A$100)-COUNTIF(Master!$A$2:$A$1
00,"<>"&WSN),"",
INDIRECT("Master!"&ADDRESS(SMALL((IF(Master!$A$2:$A$100=--WSN,ROW(Master!$A$
2:$A$100),
ROW()+ROWS(Master!$A$2:$A$100))),ROW()-ROW(A$2:A$100)+1),COLUMN(Master!A$2:A
$100),4)))
Copy A2 across to D2, fill down to say D20
(fill down just enough to cover the max expected extent of data per tech)
Cols A to D will return only the lines for tech: 603 from "Master",
with all lines neatly bunched at the top
For a clean look, suppress the display of extraneous zeros in the sheet via:
Click Tools > Options > View tab > Uncheck "Zero values" > OK
Now, just make a copy of the sheet: 603, rename it as say: 607
and we'd get the results for tech: 607.
Repeat the copy > rename sheet process
to get the rest of the tech sheets as required (a one-time job)
