Tracking attendance at training using Polls on MS Teams and Excel

Question

I am trying to establish what players are A. at training, B. not there and C. are there but not participating. I want to organise and track this data using Excel.

If I post a Poll on MS Teams, I can download the result for each player through an excel document. It is displayed in the following format:

Order of response	Player name	Poll Response
1	Player A	Yes
2	Player B	Yes
3	Player C	No
4	Player D	Yes but injured
5	Player E	No
6	Player F	No
7	Player G	Yes but injured
8	Player H	Yes
9	Player I	Yes
10	Player J	Yes
11	Player K	No

The first thing that I want to do is to arrange the names in a list according to all the A. yes, B. No, C. There but injured.

The second thing that I want to do is:

There are 45 player on the panel. If only 30 of those respond to the poll, there is no quick way to see who has and has not responded. Is there a way to have a set list. Import the above data from the poll. Automatically fill in Option D. No Contact if there is no data for their name?

The third thing that I want to do is to track this information over the course of say maybe 30 training sessions. That would be one sheet with a row for each player name their attendance for each session

kidd_ip · Answer

Worth try this:
&nbsp;

Create a Master List:

List all 45 players in one column.

Use VLOOKUP to Match Responses:

Assuming your master list is in column A and the poll responses are in columns B, C, and D:=IFERROR(VLOOKUP(A2, $B$2:$D$11, 3, FALSE), "No Contact")
This formula checks if a player’s name from the master list appears in the poll responses and returns the corresponding response. If not found, it returns “No Contact”.

3. Track Attendance Over Multiple Sessions
To track attendance over multiple sessions, you can set up a table with player names and columns for each session:

Create a Table:

Column A: Player Names
Columns B to AE (or more): Each training session

Fill in Attendance:

For each session, fill in the attendance status (e.g., “Yes”, “No”, “Yes but injured”, “No Contact”).

Use Conditional Formatting:

Highlight cells based on their values to easily visualize attendance patterns.

Forum Discussion

Tracking attendance at training using Polls on MS Teams and Excel

3. Track Attendance Over Multiple Sessions

Share

Resources