Thanks. This helped a great deal. And I was able to tweak the code for
some other uses. Now I will spend a little more time creating arrays for
somethings 'cause I'm afraid other shared users might accidentally delete
some important rows of information.

Thanks again.

"DOR" wrote in message
oups.com...
Jaye,

Here is a crude approach that should work (if coded properly!). It is
easier to describe without using your array names, although they could
be used also, but it would require OFFSETs and all that jazz ...

Assuming:

Players are on a sheet called Players
Header row is row 1 on your Players sheet and first data row is row 2,
Assume team ID is in col C and Status is in col E


On your Players sheet, in a new column (X) enter in row 2:

=IF(E2=1,C2&"-"&SUMPRODUCT(C$2:C2=C2)*(E$2:E2=1)),"") and copy down to
last player

Call that range TeamActive.

This will concatenate team_ID with a serial number representing the
first, second, third, etc. active player on each team. (The "-"
delimiter is used to avoid ambiguities that might arise from adjacent
numbers - you could use a space instead)

Allocate a new sheet to each team. In A1 of this sheet enter the team
ID for a specific team.

In B1 enter

=SUMPRODUCT((Team_ID=A1)*(Status=1))

This should count the number of active players in this team.

To list your active players:

Enter consecutive numbers 1 through n, where n represents the maximum
number of active players per team, in column A from, say Row 3, down.

In B3 enter

=IF(A3<=$B$1,MATCH($A$1&"-"&A3,TeamActive,0),"") and copy down

This creates a displacement or index for each active player in the
players sheet.

From here on all you need to enter is a formula in the remaining
columns of the team sheet such as

=IF($B3="","",INDEX(Last_N,$B3)) in C3 to get the Last Name,

and so on for the other attributes, and copy down. (Assuming Last_N
is defined as a vector - a single column range). You should get blank
cells after the last active player.

You can move the Team_ID and Active Count anywhere on your sheet, as
you wish, to suit your formatting needs.

After you test it, and it works(!) copy the sheet as many times as you
have teams and enter the appropriate team_ID in the Team_ID cell on
each sheet to get the active players for that team.

I've no doubt that this crude, but easy to follow, approach could be
refined with some array formulas but it tends to work for me and is
easy to debug.

I hope I haven't made too many typos ...

HTH