Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
list data to show up on multiple sheets | Excel Worksheet Functions | |||
Create a summary list using same fields from multiple sheets | Excel Discussion (Misc queries) | |||
Generating parts list from multiple sheets | New Users to Excel | |||
Multiple Sheets (Need to create 500 individual sheets in one workbook, pulling DATA | Excel Worksheet Functions | |||
Single list from multiple sheets | Excel Worksheet Functions |