2

I am trying to create a Fantasy Football Cheat sheet since my league drafts offline. The excel file has two sheets, one with all of the available players to draft sorted in a table, the other with my team as I draft them sorted into a table. I am trying to make it so I can mark a player with a designated letter and that will trigger the players information to be pulled into my team table.

What I have figured out is that I am able to pull the information into the other table every time I place an L (for Logan) in the drafted column using this formula:

=IF(Table2[[#All],[Drafted]]="L",Table2[Player Name], "Empty")

The issue I am having is the IF function pulls the player data, but it pulls it into the same cell in sheet 2 that the player is located in on sheet 1. I need to get it so the table looks for the next L and pulls in the player data into the next empty spot on the team table. Refer to the image below:

https://madshot.net/2af3822b36b1.png

I appreciate any suggestions to help me get past this road block.

Sardar Usama
  • 19,536
  • 9
  • 36
  • 58
LoganG
  • 23
  • 4

1 Answers1

0

You can use this formula entered at the first cell for column Player: 

=IFERROR(INDEX(Table2[Player Name],
   AGGREGATE(15,6, Table2[ADP]/(Table2[Drafted]="L"),ROW(1:1))),"")

You can use VLookup for the other columns once the players' names were imported. Alternatively you can use the same formula just by substituting Table2[Player Name] with the appropriate column, i.e. Table2[Position].

p.s. I used Table2[ADP] to benefit from the existence of the row numbers in the table, instead of the otherwise more complex ROW(Table1[Drafted])-ROW(Table1[#Headers]).

A.S.H
  • 29,101
  • 5
  • 23
  • 50