Arrows indicating position change

[Chiefee]

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.

Copy of Football Prediction V6.xlsm
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
6	Player Name	4 Pointers	3 Pointers	2 Pointers	1 Pointers	Points			Player Name	4 Pointers	3 Pointers	2 Pointers	1 Pointers	Points	Rank	2nd week rank
7	Player 12	7	7	5	5	64	-1		Player 1	3	2	5	7	35	3	8
8	Player 13	6	8	5	6	64			Player 2	3	3	4	8	37	4	3
9	Player 11	5	6	4	13	59			Player 3	5	5	3	2	43	9	2
10	Player 14	5	8	3	7	57			Player 4	3	7	7	5	52	15	6
11	Player 20	9	4	2	4	56			Player 5	6	6	2	4	50	14	9
12	Player 4	3	7	7	5	52			Player 6	6	3	4	4	45	10	5
13	Player 5	6	6	2	4	50			Player 7	4	6	7	2	50	13	13
14	Player 7	4	6	7	2	50			Player 8	2	2	6	4	30	1	1
15	Player 16	7	3	4	4	49			Player 9	2	7	6	2	43	8	12
16	Player 17	6	4	3	3	45			Player 10	4	2	4	2	32	2	4
17	Player 6	6	3	4	4	45			Player 11	5	6	4	13	59	18	16
18	Player 3	5	5	3	2	43			Player 12	7	7	5	5	64	20	20
19	Player 9	2	7	6	2	43			Player 13	6	8	5	6	64	19	19
20	Player 19	5	3	4	5	42			Player 14	5	8	3	7	57	17	17
21	Player 18	4	3	6	4	41			Player 15	5	2	3	5	37	5	7
22	Player 15	5	2	3	5	37			Player 16	7	3	4	4	49	12	10
23	Player 2	3	3	4	8	37			Player 17	6	4	3	3	45	11	11
24	Player 1	3	2	5	7	35			Player 18	4	3	6	4	41	6	15
25	Player 10	4	2	4	2	32			Player 19	5	3	4	5	42	7	14
26	Player 8	2	2	6	4	30			Player 20	9	4	2	4	56	16	18
Overall Season Score

Cell Formulas
Range	Formula
D7:D26	D7	=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:E26	E7	=VLOOKUP($D7,$L$7:$Q$26,2,0)
F7:F26	F7	=VLOOKUP($D7,$L$7:$Q$26,3,0)
G7:G26	G7	=VLOOKUP($D7,$L$7:$Q$26,4,0)
H7:H26	H7	=VLOOKUP($D7,$L$7:$Q$26,5,0)
I7:I26	I7	=VLOOKUP($D7,$L$7:$Q$26,6,0)
J7:J26	J7	=SIGN(R7-S7)
L7:L26	L7	='Players & Money'!C5
M7:P26	M7	=SUM('Week 1:Week 40'!C6)
Q7:Q26	Q7	=SUM(M7*4,N7*3,O7*2,P7*1)
R7:R26	R7	=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
Cell	Condition		Cell Format	Stop If True
J7	Other Type	Icon set		NO
J7:J26	Other Type	Icon set		NO

 

[Fluff]

How about

+Fluff 1.xlsm
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
6	Player Name	4 Pointers	3 Pointers	2 Pointers	1 Pointers	Points			Player Name	4 Pointers	3 Pointers	2 Pointers	1 Pointers	Points	Rank	2nd week rank
7	Player 12	7	7	5	5	64	0		Player 1	3	2	5	7	35	3	8
8	Player 13	6	8	5	6	64	0		Player 2	3	3	4	8	37	4	3
9	Player 11	5	6	4	13	59	2		Player 3	5	5	3	2	43	9	2
10	Player 14	5	8	3	7	57	0		Player 4	3	7	7	5	52	15	6
11	Player 20	9	4	2	4	56	-2		Player 5	6	6	2	4	50	14	9
12	Player 4	3	7	7	5	52	9		Player 6	6	3	4	4	45	10	5
13	Player 5	6	6	2	4	50	5		Player 7	4	6	7	2	50	13	13
14	Player 7	4	6	7	2	50	0		Player 8	2	2	6	4	30	1	1
15	Player 16	7	3	4	4	49	2		Player 9	2	7	6	2	43	8	12
16	Player 17	6	4	3	3	45	0		Player 10	4	2	4	2	32	2	4
17	Player 6	6	3	4	4	45	5		Player 11	5	6	4	13	59	18	16
18	Player 3	5	5	3	2	43	7		Player 12	7	7	5	5	64	20	20
19	Player 9	2	7	6	2	43	-4		Player 13	6	8	5	6	64	19	19
20	Player 19	5	3	4	5	42	-7		Player 14	5	8	3	7	57	17	17
21	Player 18	4	3	6	4	41	-9		Player 15	5	2	3	5	37	5	7
22	Player 15	5	2	3	5	37	-2		Player 16	7	3	4	4	49	12	10
23	Player 2	3	3	4	8	37	1		Player 17	6	4	3	3	45	11	11
24	Player 1	3	2	5	7	35	-5		Player 18	4	3	6	4	41	6	15
25	Player 10	4	2	4	2	32	-2		Player 19	5	3	4	5	42	7	14
26	Player 8	2	2	6	4	30	0		Player 20	9	4	2	4	56	16	18
Master

Cell Formulas
Range	Formula
J7:J26	J7	=SUMIFS($R$7:$R$26,$L$7:$L$26,D7)-SUMIFS($S$7:$S$26,$L$7:$L$26,D7)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J7:J26	Other Type	Icon set		NO

 

[Chiefee]

How about

+Fluff 1.xlsm
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
6	Player Name	4 Pointers	3 Pointers	2 Pointers	1 Pointers	Points			Player Name	4 Pointers	3 Pointers	2 Pointers	1 Pointers	Points	Rank	2nd week rank
7	Player 12	7	7	5	5	64	0		Player 1	3	2	5	7	35	3	8
8	Player 13	6	8	5	6	64	0		Player 2	3	3	4	8	37	4	3
9	Player 11	5	6	4	13	59	2		Player 3	5	5	3	2	43	9	2
10	Player 14	5	8	3	7	57	0		Player 4	3	7	7	5	52	15	6
11	Player 20	9	4	2	4	56	-2		Player 5	6	6	2	4	50	14	9
12	Player 4	3	7	7	5	52	9		Player 6	6	3	4	4	45	10	5
13	Player 5	6	6	2	4	50	5		Player 7	4	6	7	2	50	13	13
14	Player 7	4	6	7	2	50	0		Player 8	2	2	6	4	30	1	1
15	Player 16	7	3	4	4	49	2		Player 9	2	7	6	2	43	8	12
16	Player 17	6	4	3	3	45	0		Player 10	4	2	4	2	32	2	4
17	Player 6	6	3	4	4	45	5		Player 11	5	6	4	13	59	18	16
18	Player 3	5	5	3	2	43	7		Player 12	7	7	5	5	64	20	20
19	Player 9	2	7	6	2	43	-4		Player 13	6	8	5	6	64	19	19
20	Player 19	5	3	4	5	42	-7		Player 14	5	8	3	7	57	17	17
21	Player 18	4	3	6	4	41	-9		Player 15	5	2	3	5	37	5	7
22	Player 15	5	2	3	5	37	-2		Player 16	7	3	4	4	49	12	10
23	Player 2	3	3	4	8	37	1		Player 17	6	4	3	3	45	11	11
24	Player 1	3	2	5	7	35	-5		Player 18	4	3	6	4	41	6	15
25	Player 10	4	2	4	2	32	-2		Player 19	5	3	4	5	42	7	14
26	Player 8	2	2	6	4	30	0		Player 20	9	4	2	4	56	16	18
Master

Cell Formulas
Range	Formula
J7:J26	J7	=SUMIFS($R$7:$R$26,$L$7:$L$26,D7)-SUMIFS($S$7:$S$26,$L$7:$L$26,D7)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J7:J26	Other Type	Icon set		NO

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]

Oops, forgot to set the icons properly, it should be

 

[Chiefee]

Oops, forgot to set the icons properly, it should be
View attachment 32372
View attachment 32373

 

[Chiefee]

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

 

[Fluff]

You're welcome & thanks for the feedback.