Ranking scores/names from multiple sheets on summary page

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

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

like this


=IF('Client 1'!E4=SUMMARY!C7,'Client 1'!A1,IF('Client 2'!E4=SUMMARY!C7,'Client 2'!A1))

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!