Helped needed with formula moving data from one place to another

[MayeskUK1]

Hi there,

I am currently building a spreadsheet for a fantasy football (soccer) game. I've hit a bit of a stumbling block.I have created a simple table which includes players names, clubs, points etc. and a column at the end with a simple drop-down menu that allows you to select a player.

I am trying to create a formula so that when "Pick" is selected, the information in the row for a specific player is transferred to another table.I have attempted a number of IF and VLOOKUP formulas to try and transfer the data across with little success. I've also tried a few macros. Having a hard time!

So, I need a formula that extracts the 15 players with "Pick" selected from a list of 600 players into a new table.

Please Help! and Thanks for reading!

 

[FormR]

Hi, welcome to the forum.

See if you can adapt this to your actual set-up. If you have difficulties try to post back with the details where the list of players names are, where the "Pick" column is, in which cell you are placing the formula and which version of Excel you are using.

Excel 2013
	A	B	C	D	E
1	Player	Picked		Helper Formula:	6
2	Player 1	Pick		Formula Copied Down:	Player 1
3	Player 2				Player 4
4	Player 3				Player 6
5	Player 4	Pick			Player 7
6	Player 5				Player 11
7	Player 6	Pick			Player 13
8	Player 7	Pick
9	Player 8
10	Player 9
11	Player 10
12	Player 11	Pick
13	Player 12
14	Player 13	Pick
Sheet1

Cell Formulas
Range	Formula
E1	=COUNTIF($B$2:$B$600,"Pick")
E2	=IF(ROWS($E$2:E2)>$E$1,"",INDEX($A$2:$A$600,AGGREGATE(15,6,(ROW($B$2:$B$600)-ROW($B$2)+1)/($B$2:$B$600="Pick"),ROWS($E$2:E2))))

 

[MayeskUK1]

I am struggling to adapt this to my spreadsheet! Sadly, my excel skills are much worse than I thought!

The entire table is A2:K601. My player names are in the column A2:A601 and the "Pick" column is K2:K601. I am currently using N8:N22 on the same sheet as a separate table to move my 15 players names picked to.

I am also using Excel 2010.

Thanks so much for your help so far!

 

[FormR]

Hi, first place this formula in a spare cell, N7 in this example:

=COUNTIF($K$2:$K$601,"Pick")

And then in N8 and copied down:

=IF(ROWS($N$8:N8)>$N$7,"",INDEX($A$2:$A$601,AGGREGATE(15,6,(ROW($K$2:$K$601)-ROW($K$2)+1)/($K$2:$K$601="Pick"),ROWS($N$8:N8))))

 

[MayeskUK1]

That has worked an absolute dream! thanks very much!