How do I track non-movers in a weekly league table created in Excel?

Hi,

I have created a league table spreadsheet, attached. Tab 1 tracks the weekly scores of each player (inputted manually), and tab 2 shows the relative positions in a league table (updated automatically based on manual score input in tab 1).

I have a problem with one of the things I want to do in the league table (tab 2) - I want to show movement up and down (or no change) through an easy, visual, arrows system. I have used an amalgamation of formulae on the league table tab, and the upshot is that it nearly works. Nearly. The problem I have is that sometimes the column showing the "Movement" arrows doesn't work properly, because the formula for the arrows recognises the movement of the player in terms of the row on the worksheet he appears in, rather than recognising what his position is relative to the other players.

What needs to happen is that the formula for the "Movement" column (column E) needs to be based on the "Position" column (column B), rather than the absolute rankings required to generate the league table in the first place. (I know why it doesn't work - I just don't know how to fix it!)

This will all become clear when you look at the attachment. I have added notes to make it as clear as possible how this is all meant to work.

Any suggestions on how to fix the problem would be gratefully received. I am a novice Excel user, and Macros are currently a little beyond me, so if the answer could be done through formulae, that would be all the better! Equally if you need to change the way I have done something already in order to fix the problem, that's fine - if you could just please make it clear what you've done and why, that would be great - otherwise I will get hopelessly lost!

Thanks!

Try changing your formula in cell E4 to:

=TEXT(IF(E35="",0,INDEX(E$35:E$44,MATCH(C4,$C$18:$C$27,0))-B4),"↑;↓;↔")

and copy down

Brilliant - works like a treat - thanks very much, Cutter. I will change my thread to "solved", but if you have a spare two minutes and feel like explaining to me how the formula in the E column actually works, I'd be very grateful! (I cobbled it together from various other internet/forum posts, but can't claim to actually understand it that well!)

You're welcome.
The IF() is just checking to see if E35 contains something other than "". If it does then it performs the calculation.
This part: INDEX(E$35:E$44,MATCH(C4,$C$18:$C$27,0)) is finding the player's rank from the previous week
---It looks in the range E35:E44 and returns the row # of that index determined by the result of the MATCH.
---The MATCH looks for the players name in range C18:C27 and returns the position in that range (first, second, third....tenth)
Then B4 (the player's current rank) is subtracted from the previous rank
The TEXT() function is providing the symbols by formatting the result of the subtraction: positive(↑);negative(↓);zero(↔)