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.

I agree with Bill and would probably take a Data Filter path.
However, if it must be in a separate sheet, try a pivot table. Select
any cell in the data sheet, then follow the wizard launched from the
menu path Data Pivot Tables and Pivot Charts. When it is time to
drag and drop fields, drop the "Name" field in the column area far to
the left and drop the "Position" field in the Page area at the top.
Depending on your data, you may drop other fields on the top row or in
the data area but you didn't indicate that you needed more than a list.
Finish off the wizard.

At the top of the pivot table, select the "Position" from the drop down
list and presto.

- John