I'm not sure how to solve this problem, so hopefully someone can help me with
a solution... I have several sheets that contain swimmers' times, and I want
to come up with a Top 20 list for each event. Each year I will add new data,
and I want the Top 20 to update.

The data looks like this (a different sheet for each year), with 20-40 rows
of data. Columns C and D have data that's not important for the Top20. The
list continues to the right in the same manner to include 7 events.
A B C D E ... Y
Name 200 Free 200 IM Year
Jane Doe 2:15.1 2:19.3 2008
Sue Smith 2:11.6 2:39.9 2008
Ann Johnson 2:06.4 2:31.6 2008

The result that I want to achieve would pull the top 20 from all of the
sheets, and would look like this (a different list for each event):
200 Free, Top 20
Ann Johnson 2:06.4 2008
Sue Smith 2:11.6 2008
Jenny Johnson 2:12.0 2006
etc.

I have tried a few solutions, but I'm stuck on all of them:
1. I tried creating a PivotTable
Problem: I can't use multiple sheets and still manipulate the data
meaningfully
2. I tried merging all of the sheets into one sheet, and making a PivotTable
of that sheet
Problem: It's labor intensive to merge the sheets
Problem: After creating the PivotTable, it rounds the values (e.g.,
2:06.8 becomes 2:07.0)
Problem: When I sort the PivotTable for the Top20 (bottom 20,
actually), it includes empty cells as a top time when a swimmer does not have
a time for an event
3. I tried importing the data into an Access database
Problem: After importing the first sheet, I couldn't import the other
sheets into the same table
Problem: I've never really used Access, so I'm not sure what I'm doing

The best answer is Access, but due to your lack of experience with it, we'll
make it work with what you have.

I would suggest creating top 20 list per sheet (where, if I understand
correctly, each sheet is a different year). Once a year is over, that sheet
won't be updated again (at least I don't see why it would). Create a "top 20
of all time" sheet where these top 20 lists/year go. Then all you have to do
for your "top 20 ever" list is reference the other top 20/year lists.

I'm assuming you know how to get the top 20/year, so I won't go into those
steps.

"Horatio J. Bilge, Jr." wrote:

I'm not sure how to solve this problem, so hopefully someone can help me with
a solution... I have several sheets that contain swimmers' times, and I want
to come up with a Top 20 list for each event. Each year I will add new data,
and I want the Top 20 to update.

The data looks like this (a different sheet for each year), with 20-40 rows
of data. Columns C and D have data that's not important for the Top20. The
list continues to the right in the same manner to include 7 events.
A B C D E ... Y
Name 200 Free 200 IM Year
Jane Doe 2:15.1 2:19.3 2008
Sue Smith 2:11.6 2:39.9 2008
Ann Johnson 2:06.4 2:31.6 2008

The result that I want to achieve would pull the top 20 from all of the
sheets, and would look like this (a different list for each event):
200 Free, Top 20
Ann Johnson 2:06.4 2008
Sue Smith 2:11.6 2008
Jenny Johnson 2:12.0 2006
etc.

I have tried a few solutions, but I'm stuck on all of them:
1. I tried creating a PivotTable
Problem: I can't use multiple sheets and still manipulate the data
meaningfully
2. I tried merging all of the sheets into one sheet, and making a PivotTable
of that sheet
Problem: It's labor intensive to merge the sheets
Problem: After creating the PivotTable, it rounds the values (e.g.,
2:06.8 becomes 2:07.0)
Problem: When I sort the PivotTable for the Top20 (bottom 20,
actually), it includes empty cells as a top time when a swimmer does not have
a time for an event
3. I tried importing the data into an Access database
Problem: After importing the first sheet, I couldn't import the other
sheets into the same table
Problem: I've never really used Access, so I'm not sure what I'm doing

I like your suggestion (at least until I get a chance to play around with
Access more). I thought it would be easy to make the Top20 list for each
year, but I've run into a snag.

I used this formula to rank the Top20 times for each event (adapted from
Chip Pearson's site):
=IF(ROW()-ROW(Top20)+1TopN,"",SMALL(RankEvent,ROW()-ROW(Top20)+1))
** RankEvent is the range that includes all of the times (B2:B30).
** Top20 is the range that the Top20 is listed in (Z2:Z30).
** TopN is a named cell that I can change - currently it's value is 20, for
the Top20.
That worked great.

Next, I needed to get the names that correspond to each time. In some cases
there are two swimmers with the same time, so I need to take that into
account. I found a formula at
http://office.microsoft.com/en-us/ex...260381033.aspx for returning
multiple corresponding values. But to tweak it for my purposes, I get a #NUM
error. Instead of referring to a separate cell like they do in their example,
I am referring to an entry in the Top20 list. The array formula (entered in
AA2 and copied down for all cells) is:
=INDEX(Data2008,SMALL(IF(RankEvent=$Z2,ROW(RankEve nt)),ROW(1:1)),1)

The first cell gives the expected swimmer's name as a result, but all of the
other cells give the #NUM error.



"BobT" wrote:

The best answer is Access, but due to your lack of experience with it, we'll
make it work with what you have.

I would suggest creating top 20 list per sheet (where, if I understand
correctly, each sheet is a different year). Once a year is over, that sheet
won't be updated again (at least I don't see why it would). Create a "top 20
of all time" sheet where these top 20 lists/year go. Then all you have to do
for your "top 20 ever" list is reference the other top 20/year lists.

I'm assuming you know how to get the top 20/year, so I won't go into those
steps.

"Horatio J. Bilge, Jr." wrote:

I'm not sure how to solve this problem, so hopefully someone can help me with
a solution... I have several sheets that contain swimmers' times, and I want
to come up with a Top 20 list for each event. Each year I will add new data,
and I want the Top 20 to update.

The data looks like this (a different sheet for each year), with 20-40 rows
of data. Columns C and D have data that's not important for the Top20. The
list continues to the right in the same manner to include 7 events.
A B C D E ... Y
Name 200 Free 200 IM Year
Jane Doe 2:15.1 2:19.3 2008
Sue Smith 2:11.6 2:39.9 2008
Ann Johnson 2:06.4 2:31.6 2008

The result that I want to achieve would pull the top 20 from all of the
sheets, and would look like this (a different list for each event):
200 Free, Top 20
Ann Johnson 2:06.4 2008
Sue Smith 2:11.6 2008
Jenny Johnson 2:12.0 2006
etc.

I have tried a few solutions, but I'm stuck on all of them:
1. I tried creating a PivotTable
Problem: I can't use multiple sheets and still manipulate the data
meaningfully
2. I tried merging all of the sheets into one sheet, and making a PivotTable
of that sheet
Problem: It's labor intensive to merge the sheets
Problem: After creating the PivotTable, it rounds the values (e.g.,
2:06.8 becomes 2:07.0)
Problem: When I sort the PivotTable for the Top20 (bottom 20,
actually), it includes empty cells as a top time when a swimmer does not have
a time for an event
3. I tried importing the data into an Access database
Problem: After importing the first sheet, I couldn't import the other
sheets into the same table
Problem: I've never really used Access, so I'm not sure what I'm doing