There HAS to be an easier way to do this!

[Tish]

Each week we have a photographer take pictures for our highschool
football team. I then make a spreadsheet of who is in each picture so
parents can order reprints. The first column is the film roll/picture
number and then the next four or so columns record the player's number
featured in the picture. On a separate worksheet I have created
"order forms" for each family, listing the pictures that their player
is in.

The WAY I'm now doing this involves too many steps. I have been using
an auto filter to sort each column by player number and then cut and
paste the picture numbers into the order form. What I would LIKE to
do but don't know how to, is to compile a list for each player number,
indicating what pictures he's in. Is there a way to do this in ONE
step, instead of five? I thought about a formula but I don't know
what to ask in the formula? I'm sure there's someone out there who
can figure this out better than I.

I welcome any suggestions. Thanks.

 

[Bob Greenblatt]

Each week we have a photographer take pictures for our highschool
football team. I then make a spreadsheet of who is in each picture so
parents can order reprints. The first column is the film roll/picture
number and then the next four or so columns record the player's number
featured in the picture. On a separate worksheet I have created
"order forms" for each family, listing the pictures that their player
is in.

The WAY I'm now doing this involves too many steps. I have been using
an auto filter to sort each column by player number and then cut and
paste the picture numbers into the order form. What I would LIKE to
do but don't know how to, is to compile a list for each player number,
indicating what pictures he's in. Is there a way to do this in ONE
step, instead of five? I thought about a formula but I don't know
what to ask in the formula? I'm sure there's someone out there who
can figure this out better than I.

I welcome any suggestions. Thanks.

It sounds to me like you can just do a filter of the original sheet. You'll
have to filter it for each player. But, isn't it easier to filter it, print
it, filter it again, etc. than to do all that copying and pasting?

 

[John Van Voorhis]

This is off the top of my head, so use with caution.

1) Use advanced filter.
2) Create a sheet for each player number.
3) Copy the column headings to the top of the sheet.
4) Put the player number for that sheet in each column, but in separate
rows. This will be the criteria range.
5) copy the header row again below that. This will be the copy to range.
6) Probably easiest if you create names for all of these ranges.
7) Use the list of players in pictures as the data range, and enter the
other ranges in the dialog box.
8) You will then have a list of pictures which that player is in.

9) The next step is probably to make a macro for this whole process, but I'm
not sure how to do that.
-John Van Voorhis

 

[Jacques Lacroute]

Le 17/10/03 18:44, dans (e-mail address removed),

Each week we have a photographer take pictures for our highschool
football team. I then make a spreadsheet of who is in each picture so
parents can order reprints. The first column is the film roll/picture
number and then the next four or so columns record the player's number
featured in the picture. On a separate worksheet I have created
"order forms" for each family, listing the pictures that their player
is in.

The WAY I'm now doing this involves too many steps. I have been using
an auto filter to sort each column by player number and then cut and
paste the picture numbers into the order form. What I would LIKE to
do but don't know how to, is to compile a list for each player number,
indicating what pictures he's in. Is there a way to do this in ONE
step, instead of five? I thought about a formula but I don't know
what to ask in the formula? I'm sure there's someone out there who
can figure this out better than I.

I welcome any suggestions. Thanks.

I wonder wether the format in which you record who's in pict number y of
roll z is not making things more complex.

If you were recording in a databes such as :
Column 1 : roll ref + pict number
Column 2 : player number
As many rows with same column 1 data as there are players who appear on that
picture

Then you could use the dynamic table features of Excel and :

Create a dynamic table with the players as "page entry"
Ask for Excel to show all tables for the players


I've created a workobbok with
a mock up data base : sheet "data old format"
The rearranged data base as I suggest (you can do this by running the macro
called "Reformatdata" after selecting the "players area of the data base) :
sheet "data rearranged"
The basis dynamic table : sheet "Dynamic table"
And a series of sheets colled Jobloke1 through Jobloke15 that Excel
generated when I asked (ctrl Click in the "page "header of the dynamic
table; show pages...) Excel to create the individual pages for each player.
Which you could probably use as order forms.

I cannot attach this file to this message (too large), I'll send it to you
if you're interested

Hope it helps

Jacques Lacroute