One play using non-array formulae which would "auto-extract" the employee
ids into each dept's sheet from a master list ..
A sample implementation is available at:
http://cjoint.com/?lydMDFJAhn
AutoSortEmployee_InTo_Dept_SheetName.xls
Assume the master list is in sheet: Master
in cols A to B, headers in row1, data from row2 down
Using empty cols to the right of the data, say cols K onwards
List the Dept names in K1, L1 across: Mktg, Admin, Acct, etc
Put in K2: =IF($B2=K$1,ROW(),"")
Copy K2 across to M2, fill down to say M20,
to cover the max expected data in the master list
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: Mktg
With the same col headers in A1:B1
Put in A2:
=IF(ISERROR(SMALL(OFFSET(Master!$J:$J,,MATCH(WSN,M aster!$K$1:$IV$1,0)),ROWS(
$A$1:A1))),"",INDEX(Master!A:A,MATCH(SMALL(OFFSET( Master!$J:$J,,MATCH(WSN,Ma
ster!$K$1:$IV$1,0)),ROWS($A$1:A1)),OFFSET(Master!$ J:$J,,MATCH(WSN,Master!$K$
1:$IV$1,0)),0)))
Copy A2 across to B2, fill down to B20
(cover the same range size as was done in "Master"' cols K, L)
Cols A to B will return only the lines for the Dept: Mktg from "Master",
with all lines neatly bunched at the top
Now, just make a copy of the sheet: Mktg, rename it as: Admin, and you'd get
the results for Admin. Repeat the sheet copy & rename as required to
extract the lists for the rest of the depts (a one-time effort).
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"thomasNg" wrote in
message ...
dear forum,
help please.
i have 2 columns in sheet 1: col A is employee ID, col B is Dept name.
i want to search col B, match it with the employee ID in col A.
i have several other sheets so that each sheet shows only the employee
IDs for one particular dept.
so far i have used a simple if(dept="marketing",a1,"--"). i am not too
sure how to use vlookup in this case. also i dont want to skip rows.
this is what i am getting with my formula
sheet 1 sheet 2
sheet 3
1012 Acct --
1012 acct
1064 Marketing 1064 marketing
--
2066 Admin --
--
5066 Acct --
5066 acct
6654 Marketing 6654 marketing
--
sorry this post is longwinded, but i thought i'd give as much b/ground
info as possible.
thanx
thomas.
--
thomasNg
------------------------------------------------------------------------
thomasNg's Profile:
http://www.excelforum.com/member.php...fo&userid=8375
View this thread: http://www.excelforum.com/showthread...hreadid=487834