got a big spreadsheet with scores and shite, too big to print off/too complicated to email round, so basically trying to get a summary table done.

to keep it simple lets say I'm using Cells A2, B1 and B2. the rank is in A2, the score in B2 and the name in B1, i want to be able to create a wee table on a separate sheet where column A shows 1-16 (number of players) Column B returns the score based on the rank (HLookup I think) but can't for the life of me think how to get Column C to return the name...


cheers in advance.

Why not put the name in c2 to begin with?

Assuming that's not possible and assuming each rank is unique (ie only 1 player can be ranked 5) then I think it can be solved with MATCH+INDEX.
Ie ask it where rank 5 is (A2 in this case) and then the correct formula will go up a row and a column to the right to select the name.
I can't do an example now as I'm too knackered to think straight but I can look tomorrow if no one has solved your problem by then.

that might just work!

it's an existing spreadsheet where c2 is occupied, but I suppose inserting a row shouldn't fk up the formulae..... will have a look.....

nope, can't go in C2, might be able to put it in b3 though....

Your problem might be dealing with ties for position in which case I'd add to their score a tiny fraction (1/10,000th) of their row number just to differentiate

A3 is better as it is below the rank and then you can use Hlookup

is the score in the summary sheet the same score as in your first sheet as mentioned above?
or is it a different score based on the rank?

got it working under b3, but then realised this problem.....





you're some guy Mystery

want to break that down a bit for me - don't quite understand

Easiest way (not necessarily the most efficient way) to do the coding for that Barry would be to add a column, it has 1,2,3,4,5 all the way down, then add that colum multiplied by 0.0001 to the total score in each row. You'd always have the same ordering on the tie-break that way but you can always easily manually adjust it if needed by swapping the numbers around in the created column for the two tied players.

I'm sure someone will give you a more technical 1 cell way of doing it but that's the way I'd use because I'm lazy, it would work, and it wouldn't require complicated coding.

Example:

Assume column B is total score, and column C is used, column D becomes 1,2,3,4,5,etc all the way down, and in the total score you change it to XXX+(D2*0.0001) then adjust the D column manually to put the tie breaks in the order you want them.

Sorry Barry didn't see your reply.

To the score add +(ROW()/10000)