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

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.

--

Dave Peterson