I'm not familiar with any non macro formula that would do that. However, you
could use Excel's autofilter functionality (Menu path Data / Filter /
Autofilter). Then you could filter out just the positions you wanted to see.
You could copy all of sheet1 to sheet2, apply the autofilter on sheet2, and
then delete all the rows that aren't the position you want by using the
autofilter and the regular delete rows functionality.

Hope this is a start.

Bill Horton

There is also the advanced filter functionality and pivot table
functionality that may provide a solution for you.

"Lee Harris" wrote:

Say I have a list of player names on a sheet, with various columns for
different attributes, including "position"

Is it possible to have a second sheet that picks out all the players of a
certain position but has them listed without gaps

what I mean is, say I have 400 names in Sheet 1

I could easily do a formula on Sheet 2 whereby

Sheet 2: B2 = IF(Sheet1:A2="center",Sheet1:B2,"")

the problem with that is although it would automatically copy over any
player from Sheet 1 who is a center, the rows would match therefore the list
would have huge gaps in it wherever a non center was listed on Sheet 1


So, instead of

Sheet 1
other player
other player
CENTER
other player
other player
CENTER

being processed into

Sheet 2
blank
blank
CENTER
blank
blank
CENTER


I would ideally like a way that Sheet 2 would instead look like

Sheet2
CENTER (from row 3 Sheet1)
CENTER (from row 6 Sheet1)

i.e without the gaps.

I'm not sure if this is possible, and it's not like you can easily sort
because you have formulas in the cells that would be messed up if they're
referring to another sheet but in the same position.

Thanks in advance.