# Arrows indicating position change

#### Chiefee

##### New Member
Hi,

After some great help on my last problem i have 1 more and hopefully my last!

I have created a league table and i am hoping to show coloured arrows to indicate a players movement when scores change each week. I can compare the rankings from separate weeks and have made up the 2nd week scores to trial but i am unsure how to link it to the correct player within the league table.

A super touch to it would be to show how many positions they had changed to, say up 2, down 5, or same along with the colour arrows.

a=Any help is very much appreciated.

Paul.
Cell Formulas
RangeFormula
D7:D26D7=INDEX(\$L\$7:\$L\$26,MATCH(LARGE(\$R\$7:\$R\$26-ROW(\$R\$7:\$R\$26)/COUNT(\$R\$7:\$R\$26),ROW(D7)-ROW(D\$7)+1),\$R\$7:\$R\$26-ROW(\$R\$7:\$R\$26)/COUNT(\$R\$7:\$R\$26),0))
E7:E26E7=VLOOKUP(\$D7,\$L\$7:\$Q\$26,2,0)
F7:F26F7=VLOOKUP(\$D7,\$L\$7:\$Q\$26,3,0)
G7:G26G7=VLOOKUP(\$D7,\$L\$7:\$Q\$26,4,0)
H7:H26H7=VLOOKUP(\$D7,\$L\$7:\$Q\$26,5,0)
I7:I26I7=VLOOKUP(\$D7,\$L\$7:\$Q\$26,6,0)
J7:J26J7=SIGN(R7-S7)
L7:L26L7='Players & Money'!C5
M7:P26M7=SUM('Week 1:Week 40'!C6)
Q7:Q26Q7=SUM(M7*4,N7*3,O7*2,P7*1)
R7:R26R7=RANK(Q7,\$Q\$7:\$Q\$26,1)+COUNTIFS(\$Q\$7:\$Q\$26,Q7,\$M\$7:\$M\$26,"<"&M7)+COUNTIFS(\$Q\$7:\$Q\$26,Q7,\$M\$7:\$M\$26,M7,\$N\$7:\$N\$26,"<"&N7)+COUNTIFS(\$Q\$7:\$Q\$26,Q7,\$M\$7:\$M\$26,M7,\$N\$7:\$N\$26,N7,\$O\$7:\$O\$26,"<"&O7)+COUNTIFS(\$Q\$7:\$Q\$26,Q7,\$M\$7:\$M\$26,M7,\$N\$7:\$N\$26,N7,\$O\$7:\$O\$26,O7,\$P\$7:\$P\$26,"<"&P)
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
J7Other TypeIcon setNO
J7:J26Other TypeIcon setNO

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### Fluff

##### MrExcel MVP, Moderator
+Fluff 1.xlsm
DEFGHIJKLMNOPQRS
6Player Name4 Pointers3 Pointers2 Pointers1 PointersPointsPlayer Name4 Pointers3 Pointers2 Pointers1 PointersPointsRank2nd week rank
7Player 127755640Player 132573538
8Player 136856640Player 233483743
9Player 1156413592Player 355324392
10Player 145837570Player 4377552156
11Player 20942456-2Player 5662450149
12Player 43775529Player 6634445105
13Player 56624505Player 74672501313
14Player 74672500Player 822643011
15Player 167344492Player 9276243812
16Player 176433450Player 1042423224
17Player 66344455Player 1156413591816
18Player 35532437Player 127755642020
19Player 9276243-4Player 136856641919
20Player 19534542-7Player 145837571717
21Player 18436441-9Player 1552353757
22Player 15523537-2Player 167344491210
23Player 23348371Player 176433451111
24Player 1325735-5Player 18436441615
25Player 10424232-2Player 19534542714
26Player 82264300Player 209424561618
Master
Cell Formulas
RangeFormula
J7:J26J7=SUMIFS(\$R\$7:\$R\$26,\$L\$7:\$L\$26,D7)-SUMIFS(\$S\$7:\$S\$26,\$L\$7:\$L\$26,D7)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
J7:J26Other TypeIcon setNO

#### Chiefee

##### New Member
+Fluff 1.xlsm
DEFGHIJKLMNOPQRS
6Player Name4 Pointers3 Pointers2 Pointers1 PointersPointsPlayer Name4 Pointers3 Pointers2 Pointers1 PointersPointsRank2nd week rank
7Player 127755640Player 132573538
8Player 136856640Player 233483743
9Player 1156413592Player 355324392
10Player 145837570Player 4377552156
11Player 20942456-2Player 5662450149
12Player 43775529Player 6634445105
13Player 56624505Player 74672501313
14Player 74672500Player 822643011
15Player 167344492Player 9276243812
16Player 176433450Player 1042423224
17Player 66344455Player 1156413591816
18Player 35532437Player 127755642020
19Player 9276243-4Player 136856641919
20Player 19534542-7Player 145837571717
21Player 18436441-9Player 1552353757
22Player 15523537-2Player 167344491210
23Player 23348371Player 176433451111
24Player 1325735-5Player 18436441615
25Player 10424232-2Player 19534542714
26Player 82264300Player 209424561618
Master
Cell Formulas
RangeFormula
J7:J26J7=SUMIFS(\$R\$7:\$R\$26,\$L\$7:\$L\$26,D7)-SUMIFS(\$S\$7:\$S\$26,\$L\$7:\$L\$26,D7)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
J7:J26Other TypeIcon setNO

View attachment 32360
Thank you for your help once again.

It almost works. im not sure if my ranking table is getting confused as the majority of arrows are correct but the odd one is 1 position out of what the arrow states. My method was to copy the current rank R7:R26 each week to Column S.

By the attached chart you hav displayed, players 11 & 16 i think should have a green arrow and player 20 a red arrow.

#### Fluff

##### MrExcel MVP, Moderator
Oops, forgot to set the icons properly, it should be

#### Chiefee

##### New Member
That seems to have solved it. I shall give it full test run soon to make sure it all works smoothly... thank again!

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,982
Messages
5,856,649
Members
431,826
Latest member
caseyjbrett

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back