Automatic List Grab?

[Lee Harris]

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.

 

[William Horton]

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.

 

[John Michl]

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

 

[Dave Peterson]

I'd use the data|autofilter suggestion, too and try to live with the data on one
sheet.

Both Debra Dalgleish and Ron de Bruin may have solutions for you:

Debra's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

or

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

And Ron de Bruin's easyfilter.
http://www.rondebruin.nl/easyfilter.htm