Ranking scores/names from multiple sheets on summary page

**Graphire** · 11-14-2008, 11:06 PM

Hey everyone,

Here is what I am trying to accomplish for a class of mine...
Each student has their own profile sheet. Among other things it will have their name and score. Such that name is in cell A1, and score in B4 or something.

I would like to have a summary page that will display the top 5 scores and the corresponding names. I used the "=Large" formula to get the top 5 scores. Now I need to reference the name that goes with each score.

I'd also like the formula to automatically adjust for new sheets (students) being added to the workbook. If I have a constant beginning and end sheet this should be sufficient right?

Thanks so much...

Jon

**sglife** · 11-16-2008, 09:13 PM

Please give us some details about your worksheet structure, or just attach the file.
I think Vlookup() or Index(match()) can help you

**Graphire** · 11-17-2008, 05:49 PM

Instructions are on the first sheet...

I did try and get those to work as they seem to be the answer. It should be pretty simple, I just don't use Excel too often and couldn't quite grasp how to make them work across sheets. They seem to want columns and such that are next to each other.

Thanks again, really appreciate it...

**sglife** · 11-17-2008, 10:56 PM

I think you can use the nested if function if there are only 6 clients

like this

Please Login or Register  to view this content.

or you can add a master sheet linked to all the clients sheet, then the formula will be very easy. Use the Index (match()) formula

**ravishankar** · 11-18-2008, 01:17 AM

Hi
try this macro
Ravi

**Graphire** · 11-18-2008, 03:08 AM

Both of those work great with the 6 clients, is there a way to write this formula so it will create this master table based on a range of sheets. So that when a new client sheet is added it will automatically search their sheet as well.

Is having a master table and then using vlookup the only well to get Excel to do something like this where the data is not in adjacent columns?

**ravishankar** · 11-18-2008, 04:11 AM

Hi
The macro will work for any number of clients/students you add.
Ravi

**Graphire** · 11-18-2008, 06:25 PM

Thank you...

**Graphire** · 11-18-2008, 08:08 PM

How can I set it up so if two people have the same score it will report the unique names.

Is there a way to add a simple command to move the score data one cell to the left, so that when the next instance runs it will not see it?

I changed the formula to fit the layout of my Summary page, so just need to know how to move it if there isn't a cleaner way.

Thanks!