Fantasy Football: Draft day spreadsheet needs help auto filling league teams

Hello All,

This is my first time here as well as my first post so be kind haha.

I am an avid Fantasy Football player and just recently got interested in making excel sheets to help keep track of my teams and leagues. Currently I have started building a Draft Day spreadsheet that will help me track the players drafted.

Sheet 1: Has the full draft order (round by round) listed in Column C. In Column A I have the open cells which will track all the picks/selections. So as soon as a team selects a player, I would enter in the Player Name and continue to fill the draft selections down column A.

In Columns G and H, I have all the players who can be selected/drafted along with their position they play.

That is just a quick and not fully detailed explanation of the Sheet 1.

Here is my REAL question I need help with...On Sheet 2, I have all the league teams listed with all their open roster slots which shows who is on their team. As the draft begins and progresses, I am trying to figure out how I can have the Sheet 2 auto filled when a player from Sheet 1 is draft/selected in Column A. I want the player selected to be placed onto Sheet 2 under the team name that picked the player and to go into the correct position slot for the team too.

Does that make sense? I didn't want to make this post tooo elaborate until someone responds with interest or know how for figuring the solution out.

I know there is a way to do this but I am not experienced enough with Conditional Formatting, IFTHEN, COUNTIF, or any other formula that would help make this possible.

Please let me know if you can/will help me with this project.


Thank you and have a great day/night.

Welcome to the forum!

Try the following in Sheet2, cell B3. It should be array-entered (confirmed with Ctrl+Shift+Enter instead of Enter):

=IFERROR(INDEX(Sheet1!$A$2:$A$9,SMALL(IF((Sheet1!$C$2:$C$9=A$1)*(Sheet1!$B$2:$B$9=A3),ROW(Sheet1!$C$2:$C$9)-ROW($C$2)+1),COUNTIF(A$3:A3,A3))),"")

Fill down through B10, then copy over to F3 and repeat for the other teams. For teams 5-8 you'll need to change the A3s to A16s. There will obviously be more than 8 players drafted, so you'll also want to change all of the $9s in the formula to whatever row the final pick will be in. Take a look at the attachment to see if I've got it right:

Thank you CAntosh for the welcoming and for replying back so quickly.

The file you sent back looks to be working like I want it too! Thank you very much. I am going to tinker around and try to get the entire Draft pick list created and working too, if for some reason I am struggling, would you be able to assist further?


Much appreciated.

Glad to help!

If you run into issues related to the initial problem or my solution, go ahead and post about them in this thread. I'll be able to see that you posted when I check in, and the thread will be bumped on the forum at large so that others can see it and contribute, too.

If you end up having new, barely related issues/ideas/questions, make a new thread and consider including a link to this one.

Good luck!