form question

[JR]

Hello and thanks for looking at my project. I would like to create a
form on sheet 2 getting data from sheet1 and I am using vlookup but
I can only get 1 row. The first column (A) of my data range is Team
Name, The 2nd column (B) is Player and each column after that has
some sort of stat. There are 150 rows of players names and 10 Team names and
all sorted by entire Player column. The first sheet must remain sorted by
Player,
so that means the teams are all mixed up but players should be in
alphabetical order when transferred to sheet 2.
On sheet2 in cell A1 I would type the name of the team that I would
like stats on. Row 2 cell A2 would start with player, B2 C2 D2and so
on would have selected stats all from sheet1. Vlookup did great for row 2
but
how do I get to players 2 thru 15. When completed, my form should
return, when I type a team name in cell A1, all players from that team and
stats in
the 15 rows for that team so I can print each team separately. Hope this is
not to confusing. Thanks
again for looking. John

 

[Otto Moehrbach]

John
If I wanted to do this manually I would use Data - Auto Filter by Team
on the first sheet. Then I would use GoTo and select Special and Visible
cells only. Copy the selected cells and paste them to the second sheet.
Done.
But if I was doing this repeatedly, as I think you are, I would automate
it. I would have a drop-down cell on sheet 2, cell A1. A click on the down
arrow of this cell would display a list of all the teams. I would select a
team and all the players of that team would appear below A1. Done.
Which would you prefer? If you want to automate it and need help with
it, send me a your file (if it's not too big, Send a sample if it's a big
file.) with an explanation, maybe an example, of what you want to happen.
Remove "cobia97" from my email address. HTH Otto