LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 135
Default Top 20 list from multiple sheets

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
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
list data to show up on multiple sheets Onehundone Excel Worksheet Functions 3 June 28th 08 07:45 PM
Create a summary list using same fields from multiple sheets PaulG Excel Discussion (Misc queries) 1 September 6th 07 03:14 PM
Generating parts list from multiple sheets Prescher New Users to Excel 1 April 7th 07 12:14 AM
Multiple Sheets (Need to create 500 individual sheets in one workbook, pulling DATA Amaxwell Excel Worksheet Functions 4 August 17th 06 06:23 AM
Single list from multiple sheets Brian H Excel Worksheet Functions 1 November 12th 04 09:21 PM


All times are GMT +1. The time now is 04:00 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"