Automatically create subset of records on different worksheet

> .. The question is: is this possible in Excel?

Here's a possible way to deliver it in Excel ...

 

Assume your 14 colsource data is in sheet: x,

cols A to N, data in row2 down,

with the key col = col F (Category)

List in P1 across the various categories, eg: Admin, etc

Put in P2:  =IF(OR($F2="",P$1=""),"",IF($F2=P$1,ROW(),""))

Copy P2 across/fill down to cover the max expected extent of source data, eg down to row 1500?

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

**In Excel 2007, it should be Formulas > Defined Name

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 from a post by Harlan) 

Note: The workbook is presumed saved beforehand

Then, in a new sheet named after one of the Categories, eg:  Admin

With the same col headers pasted into A1:N1

Put in A2:

=IF(ROWS($1:1)>COUNT(OFFSET(x!$O:$O,,MATCH(WSN,x!$P$1:$IV$1,0))),"", INDEX(x!A:A,MATCH(SMALL(OFFSET(x!$O:$O,,MATCH(WSN,x!$P$1:$IV$1,0)),ROWS($1:1)),OFFSET(x!$O:$O,,MATCH(WSN,x!$P$1:$IV$1,0)),0)))

Copy A2 across to N2, fill down to say N200, to cover the max expected extent for any Category. Cols A to N will return only the lines for the clategory Admin from the source sheet: x, with all lines neatly bunched at the top. Now, just dress the sheet up as desired, then make copies of it. Rename each copy as the other Categories (a one time job) and you'd get the corresponding results for each of those Categories. Adapt to suit .. 

I've actually tried to use this in a spreadsheet of my own, the formula I am using is here:

=IF(ROWS($1:1)>COUNT(OFFSET(List!$X:$X,,MATCH(WSN,List!$Y$1:$BF$1,0))),"", INDEX(List!A:A,MATCH(SMALL(OFFSET(List!$X:$X,,MATCH(WSN,List!$Y$1:$BF$1,0)),ROWS($1:1)),OFFSET(List!$X:$X,,MATCH(WSN,List!$Y$1:$BF$1,0)),0)))

I have a list of employees in one tab called "employees" and I have configured another tab to pull the "employee" info into a certain format on a worksheet called "Full List".  All data on "full list" is running off of vlookups on  the employee ID number.

So I am now trying to pull the row data for employees on "full list" into separate sheets by country (column C on sheet "full list").  Each worksheet is named after a separate country (Kenya, Indonesia, etc.) and the column formatting in these sheets matches that of "full list".  

It looks like I have the first two parts of these instructions working properly, however I can't get the formula to work:

=IF(ROWS($1:1)>COUNT(OFFSET(List!$X:$X,,MATCH(WSN,List!$Y$1:$BF$1,0))),"", INDEX(List!A:A,MATCH(SMALL(OFFSET(List!$X:$X,,MATCH(WSN,List!$Y$1:$BF$1,0)),ROWS($1:1)),OFFSET(List!$X:$X,,MATCH(WSN,List!$Y$1:$BF$1,0)),0)))

I have applied this formula to my country sheets and pulled it across to column W and down 200 rows and I find that there are "#N/A" values for all rows that would have employee data.  So for "Indonesia", I have 101 rows with "#N/A" and the rest of the rows are blank.  If I go back to sheet "full list", I find that I have 101 Indonesian employees, so it looks like it is partially working, I just can't figure out what isn't working properly.

Any help is much appreciated!  Please let me know if you have any questions or if I can provide an example for further clarification.

After a mere 5-10 min set-up time, it'll also be a snap, hands-free run in Excel too, I'd reckon

Hey Daniel and Max.

Thank you so much for your suggestions. This would be a snap in Access and that may be the way to go.

John