How to sort data from one worksheet into other worksheets?

[Andrea]

Hi! I am making an Excel workbook in which we can keep and assign tasks to
various staff. I have one main worksheet, in which we will be assigning the
tasks (using a drop-down list in the C-Column). I want to create other
worksheets for each staff that shows only the task(s) row(s) from the main
worksheet that is assigned to that person. Does anyone know of an easy way
to do this?

 

[Pete_UK]

I would insert a new column A in your main sheet, label it as Ref in
A1 and put this formula in A2:

=IF(D2="","-",D2&"_"&COUNTIF(D$22,D2))

and then copy this down as far as you like (eg to row 1000). Suppose
you have the initials abc, klm, xyz in column D (was column C), then
you would see something like this in column A:

Ref
klm_1
xyz_1
klm_2
abc_1
klm_3
abc_2
xyz_2
klm_4
-
-
-

and so on. What this is doing is giving you a unique sequential
reference for each of your initials.

Then in the sheet for abc, I would use column A to record where there
is a match for that person in the main sheet, so label it Match in A1
and put this formula in A2:

=IF(ISNA(MATCH("abc_"&ROW(A1),Main!A:A,0)),"-",MATCH("abc_"&ROW
(A1),Main!A:A,0))

and copy this down as far as you like. Using the above example, you
would see:

Match
5
7
-
-
-

and so on, which are the rows on the main sheet where there is a match
for abc's allocated tasks. You would have similar formulae on the
sheets for klm and for xyz, though you would use those initials in
turn rather than abc. Then you would have this formula in B2 on each
of those sheets:

=IF($A2="-","",INDEX(Main!B:B,$A2))

to bring across the data from column B of the main sheet. This formula
can be copied across to extract the data from other columns as
required. You may need to format some of the cells to suit the data
(eg dates in the main sheet should be formatted as dates in the
subsidiary sheets). Then all the formulae in row 2 of these sheets can
be copied down as far as you need them. Column A can be hidden on each
sheet, so that they still look like what you have at the moment.

Hope this helps.

Pete

 

[Otto Moehrbach]

I take it that Column C has the staff list (in each drop-down). One way
would be to sort the table by Column C. This would put all the tasks for
each staff member in a group. Copy/paste from there to the individual
sheets. You can also automate this with VBA code. Post back if you want to
try automation. HTH Otto