Fantasy Baseball Question in Excel

[tommade]

I am using excel to take projections for my upcoming fantasy baseball draft. I will use this to draft my team. My main question is
1) can how can I remove players who have been drafted so my file no longer counts them as a future available pick?

And 2) is there a way to post player names (or at least my projected points) for the next 3 players (Undrafted) at each available Position? The idea here would be to for the cell to update following every drafted player.

Would anyone be able to please explain to me how this can be done?

 

[Amnesiac]

For number 1 you could use a dynamic list. There is a list drop down list that is dynamic, meaning as players are picked they will automatically be removed from the list and don't show up. You can see how I added one to my Super Bowl Squares template here.

For number 2, you want to display the next best available player for each position based on some prior ranking system, is that correct?

 

[RCBricker]

I am using excel to take projections for my upcoming fantasy baseball draft. I will use this to draft my team. My main question is
1) can how can I remove players who have been drafted so my file no longer counts them as a future available pick?

And 2) is there a way to post player names (or at least my projected points) for the next 3 players (Undrafted) at each available Position? The idea here would be to for the cell to update following every drafted player.

Would anyone be able to please explain to me how this can be done?

I set up a Four column data set

column A is player name
column B is Position
Column C is Weight (or draft score)
Column D is Drafted/Undrafted

Below my data set I set my three row table to show the top three players for a given position.

So that small table is

Column A Player name
Column B is position
And column C is Weight or draft score

In column A I have the following formula

=INDEX($A$1:$A$37,MATCH(1,($B41=$B$1:$B$37)*($C41=$C$1:$C$37),0))

****** the above formula is an ARRAY FORMULA and must be set with CTRL+ALT+ENTER




Column B is a data validation list in the first cell (and the two rows below equal the first row) which you select the position

column C has the following formula which will select the three top weighted players that remain undrafted.

=MAX(LARGE(IF($B$1:$B$37=$B41,IF($D$1:$D$37="Undrafted",$C$1:$C$37)),ROW(1:1)))

****** the above formula is an ARRAY FORMULA and must be set with CTRL+ALT+ENTER

Remember to change the range references to the proper columns/ranges of your data.

Thanks,

 

[tommade]

For number 2, you want to display the next best available player for each position based on some prior ranking system, is that correct?

Yes exactly.
Thanks for your help with #1

 

[tommade]

I set up a Four column data set
,

Thank you so much. I will be able to test this tonight. Hopefully I don't have any further questions. Thank you.

 

[tommade]

****** the above formula is an ARRAY FORMULA and must be set with CTRL+ALT+ENTER

I am unable to paste the code using this CTRL+ALT+ENTER, is this the only way to make it work?

 

[tommade]

I am unable to paste the code using this CTRL+ALT+ENTER, is this the only way to make it work?

bump