This is probably something that should be easy to do, but I am having a
block...

I am a track coach, and I want to be able to have my alphabetical roster
displayed to post for a meet with the events the kids are entered in listed
in a chart next to their names.

I want to be able to take this chart and be able to list the events along
with all the kids that are entered. I'd like to be able to go back and forth
between these as I change the line ups so kids aren't entered in too many
events.

Here is a small example of what I would like to have the two charts look
like. It wouldn't have to be exactly like this, but similar:

Anderson 100 200 LJ
Brady LJ Shot
Chavez 100 200 LJ 4x100
Dobie Shot 100
England 4x100 100
Flynn 4x100 200
Grabowski 4x100 LJ Shot


100
Anderson
Chavez
Dobie
England

200
Anderson
Chavez
Flynn

LJ
Anderson
Brady
Chavez
Grabowski

Shot
Brady
Dobie
Grabowski

4x100
Chavez
England
Flynn
Grabowski

Anne,

Here is what I would do.

Copy this code, and insert it into a codemodule of your workbook. If you have never used macros,
then visit

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Here is the code to copy:

'###########################

Public Function WhatFilter() As String

Dim iCol As Integer
Dim rFiltered As Range
Dim mySht As Worksheet

Set mySht = Application.Caller.Parent

If mySht.FilterMode = False Then
WhatFilter = "None"
Exit Function
End If

Set rFiltered = mySht.AutoFilter.Range

For iCol = rFiltered.Cells(1).Column To rFiltered.Cells(rFiltered.Cells.Count).Column
If mySht.AutoFilter.Filters(iCol).On Then
WhatFilter = rFiltered.Cells(1, iCol).Value
Exit Function
End If
Next iCol

End Function

'###########################

Then in cell A1, enter the formula

=WhatFilter()

Format the cell with a really large font...

In Cell A2, enter Name, and then list your athletes in alphabetical order starting in cell A3.

In cell B2, start listing your events by title, going across row 2. This will create a table.

Set your print area to be A1:A??? with A??? being your last athlete's name.

Then enter Yes in the table where the row with the athlete's name intersects with the event's title,
where you want that athlete to compete in that event. Since you see all events and all athletes'
names, you will have a good overview of conflicts and participation.

When you are done with the entry of Yes values, select your table (A2, down and across, to get all
the names and events) and then use Data / Filter... Autofilter.

All the cells in row 2 will get a little dropdown. For each dropdown in turn, select "Yes". This
will show the athletes particpating in that event, and change the value in cell A1 to the event
name. Print the sheet. Then reselect the dropdown, and select "Show All" and continue on for the
next event.

HTH,
Bernie
MS Excel MVP

"AnneF" wrote in message
...
This is probably something that should be easy to do, but I am having a
block...

I am a track coach, and I want to be able to have my alphabetical roster
displayed to post for a meet with the events the kids are entered in listed
in a chart next to their names.

I want to be able to take this chart and be able to list the events along
with all the kids that are entered. I'd like to be able to go back and forth
between these as I change the line ups so kids aren't entered in too many
events.

Here is a small example of what I would like to have the two charts look
like. It wouldn't have to be exactly like this, but similar:

Anderson 100 200 LJ
Brady LJ Shot
Chavez 100 200 LJ 4x100
Dobie Shot 100
England 4x100 100
Flynn 4x100 200
Grabowski 4x100 LJ Shot


100
Anderson
Chavez
Dobie
England

200
Anderson
Chavez
Flynn

LJ
Anderson
Brady
Chavez
Grabowski

Shot
Brady
Dobie
Grabowski

4x100
Chavez
England
Flynn
Grabowski

Anne,

Sorry - Just one change, in the function definition and in how it is used.

Public Function WhatFilter(myR As Range) As String
instead of
Public Function WhatFilter() As String

and in cell A1, enter

=WhatFilter(A2:?????)

where the ????? is the lower right cell of your table.

If you can't get this to work, I will send you a working version.

HTH,
Bernie
MS Excel MVP


"AnneF" wrote in message
...
This is probably something that should be easy to do, but I am having a
block...

I am a track coach, and I want to be able to have my alphabetical roster
displayed to post for a meet with the events the kids are entered in listed
in a chart next to their names.

I want to be able to take this chart and be able to list the events along
with all the kids that are entered. I'd like to be able to go back and forth
between these as I change the line ups so kids aren't entered in too many
events.

Here is a small example of what I would like to have the two charts look
like. It wouldn't have to be exactly like this, but similar:

Anderson 100 200 LJ
Brady LJ Shot
Chavez 100 200 LJ 4x100
Dobie Shot 100
England 4x100 100
Flynn 4x100 200
Grabowski 4x100 LJ Shot


100
Anderson
Chavez
Dobie
England

200
Anderson
Chavez
Flynn

LJ
Anderson
Brady
Chavez
Grabowski

Shot
Brady
Dobie
Grabowski

4x100
Chavez
England
Flynn
Grabowski

Bernie-

Thanks for your help. I am trying my first attempt at macros, so I
appreciate the link to the macros help. I'm having a little trouble figuring
out how to access the module I inserted once back in Excel. It is not
showing up when I press Alt F8. I must be saving it in the wrong place, I
suppose.

I may have to wait for my daugther to get home...She's taking AP Computer
Science and should be able to help. I'll plug along until then...

Anne

I got it to work! Thanks a lot. I didn't even need my daughter's help...

I think I will end up cutting and pasting each event list into another
document, but this will still end up saving a lot of time. I appreciate
your help!