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$2
2,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