# Tracking player participation throughout season

#### John_Wh

##### Board Regular
I've got a roster report with game number in first column (A) and player name in 4th column (D). Would like to represent the season in another way. Want the list of players on the team in one column and the game # in the top row. With the corresponding cells either having an X in them or possibly a color.

How do I load the table? This is small example, I've got 82 games and a roster list of 30+ players with about 20 in every game.

[IMG]http://i64.tinypic.com/nn7ehj.jpg[/IMG]

Last edited:

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### John_Wh

##### Board Regular
I know I can conditional format for color once I get the "X"'s in the cells

#### John_Wh

##### Board Regular
When I applied it to the sample data on my spreadsheet it worked well, but when I plugged it into my actual data page it didn't. the cell that corresponds to G4 in your example is actually P3, H4 is actually Q3.

#### Fluff

##### MrExcel MVP, Moderator

How did you modify the formulae?

#### John_Wh

##### Board Regular
I didn't. I just plugged

=INDEX(\$D\$2:\$D\$17,MATCH(0,INDEX(COUNTIF(\$G\$3:G3,\$D\$2:\$D\$17),0),0)) into P3

=IF(COUNTIFS(\$A\$2:\$A\$17,H\$3,\$D\$2:\$D\$17,\$G4)>=1,"X","") into Q3

here is the actual sheet

[IMG]http://i63.tinypic.com/2z8qfkx.jpg[/IMG]

#### Fluff

##### MrExcel MVP, Moderator

You need to change the cell references
=INDEX(\$D\$2:\$D\$17,MATCH(0,INDEX(COUNTIF(\$P\$2:P2,\$D\$2:\$D\$17),0),0))
=IF(COUNTIFS(\$A\$2:\$A\$17,Q\$2,\$D\$2:\$D\$17,\$P3)>=1,"X","")

You will also need to change the 17 to reflect the last row of data

#### John_Wh

##### Board Regular
Changed the 17 to 1600 in both formulas and it's perfect. Fluff, you've been helpful numerous times wish I could buy you multiple rounds of your favorite beverage!

Till next time ... Cheers

#### Fluff

##### MrExcel MVP, Moderator
Glad to help & thanks for the feedback

Replies
0
Views
218
Replies
2
Views
401
Replies
1
Views
203
Replies
2
Views
458
Replies
4
Views
258