Dynamic Images in League Table

[William53]

Hi All
I have created a league table for the Australian A-League that works dynamically when the results are inputted into the main datasheet. I would now like to create logos next/adjacent to the teams that move dynamically as the team moves up and down the league, as the results are inputted. Below is the league. Is it possible to create a formula that allows the right badge/crest to be placed in the badge column and this changes when the teams move up or down? I have the badges on a separate sheet and I have tested that I can display them using named ranges and it works (the only problem is, all the guidance I have found works on dropdown which is not practical). Any guidance will be greatly appreciated.

 

[ExceLoki]

i have a solution but parts of it wont show using xl2bb.
you can download it here:
Loading Google Sheets

 

[William53]

i have a solution but parts of it wont show using xl2bb.
you can download it here:
Loading Google Sheets

Hi ExceLoki
I have loaded the two sheets into my excel. I have changed the crests to suit the A League. Many thanks for that.
I have a calculations sheet of which the league table is the output.
When I try to place the same formulas into the "Standings" sheet I get the #Name or #value errors.
Any advice

 

[Joe4]

ExcelLoki,
Please heed rule 4 here: Message Board Rules

 

[ExceLoki]

Hi ExceLoki
I have loaded the two sheets into my excel. I have changed the crests to suit the A League. Many thanks for that.
I have a calculations sheet of which the league table is the output.
When I try to place the same formulas into the "Standings" sheet I get the #Name or #value errors.
Any advice

can you use xl2bb to show what problems you are having?

 

[William53]

Hi ExcelOki
Please find below your sheet which i am trying to insert the formula into:

A League Stats.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	position	teams	badge	teams	p	w	d	l	gf	ga	gd	pts
2	1	Sydney FC		Sydney FC		4	6	5	19	19	0	31
3	2	Adelaide Utd		Adelaide Utd	15	5	6	4	23	20	3	30
4	3	Western Utd		Western Utd	14	9	2	3	17	11	6	29
5	4	Melbourne City		Melbourne City	15	8	4	3	31	21	10	28
6	5	Wellington Phoenix		Wellington Phoenix	12	5	3	4	15	18	-3	27
7	6	Macarthur FC		Macarthur FC	13	6	4	3	21	19	2	22
8	7	Melbourne Victory		Melbourne Victory	12	5	3	4	16	15	1	18
9	8	Newcastle Jets		Newcastle Jets	12	4	3	5	20	21	-1	15
10	9	WS Wanderers		WS Wanderers	13	3	3	7	13	20	-7	12
11	10	Perth Glory		Perth Glory	11	3	2	6	9	10	-1	11
12	11	Central C. Mariners		Central C. Mariners	12	3	2	7	17	20	-3	11
13	12	Brisbane Roar		Brisbane Roar	12	3	2	7	11	18	-7	11
Standings

Cell Formulas
Range	Formula
B2:B13	B2	=Standings!$D2

The sheet below is what I am trying to copy on the above sheet:

A League Stats.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
3	Position	Column2	Cresst	Teams	P	W	D	L	GF	GA	GD	PTS
4	1	Melbourne City		Melbourne City	16	8	5	3	33	23	10	29
5	2	Western Utd		Western Utd	14	9	2	3	17	11	6	29
6	3	Macarthur FC		Macarthur FC	13	6	4	3	21	19	2	22
7	4	Adelaide Utd		Adelaide Utd	15	5	6	4	23	20	3	21
8	5	Melbourne Victory		Melbourne Victory	12	5	3	4	16	15	1	18
9	6	Sydney FC		Sydney FC	15	4	6	5	19	19	0	18
10	7	Wellington Phoenix		Wellington Phoenix	12	5	3	4	15	18	-3	18
11	8	Newcastle Jets		Newcastle Jets	12	4	3	5	20	21	-1	15
12	9	Perth Glory		Perth Glory	12	3	3	6	11	12	-1	12
13	10	WS Wanderers		WS Wanderers	13	3	3	7	13	20	-7	12
14	11	Central C. Mariners		Central C. Mariners	12	3	2	7	17	20	-3	11
15	12	Brisbane Roar		Brisbane Roar	12	3	2	7	11	18	-7	11
Output

Cell Formulas
Range	Formula
E4:L15	E4	=VLOOKUP($D4,Calculations!$C$3:$K$14,COLUMN()-3,FALSE)
D4:D14	D4	=OFFSET(Calculations!$C$2,MATCH(A4,Calculations!$O$3:$O$14,0),0)
B4:B15	B4	=$D4

I cant seem to get the formulae above to work in the standings sheet

Many thanks

William

 

[ExceLoki]

would you be able to share the calculations sheet? it's hard to reference something when cannot see it.

 

[William53]

Please find calculations sheet as requested, this is fed from a normal table which has the results from each match already played

A League Stats.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O
2		P	W	D	L	GF	GA	GD	Pts	GD*0.01	GA*0.001	PTS+GD+GA	Rank
3	Melbourne City	16	8	5	3	33	23	10	29	0.1	0.023	29.123	1
4	Perth Glory	12	3	3	6	11	12	-1	12	-0.01	0.012	12.002	9
5	Western Utd	14	9	2	3	17	11	6	29	0.06	0.011	29.071	2
6	WS Wanderers	13	3	3	7	13	20	-7	12	-0.07	0.02	11.95	10
7	Macarthur FC	13	6	4	3	21	19	2	22	0.02	0.019	22.039	3
8	Newcastle Jets	12	4	3	5	20	21	-1	15	-0.01	0.021	15.011	8
9	Adelaide Utd	15	5	6	4	23	20	3	21	0.03	0.02	21.05	4
10	Sydney FC	15	4	6	5	19	19	0	18	0	0.019	18.019	6
11	Wellington Phoenix	12	5	3	4	15	18	-3	18	-0.03	0.018	17.988	7
12	Melbourne Victory	12	5	3	4	16	15	1	18	0.01	0.015	18.025	5
13	Brisbane Roar	12	3	2	7	11	18	-7	11	-0.07	0.018	10.948	12
14	Central C. Mariners	12	3	2	7	17	20	-3	11	-0.03	0.02	10.99	11
Calculations

Cell Formulas
Range	Formula
C3:C14	C3	=UNIQUE(League!C2:C80,FALSE,FALSE)
D3:D14	D3	=COUNTIF(League1[[Home]:[Away]],Calculations!C3)
E3:E14	E3	=COUNTIFS(League1[Home],Calculations!C3,League1[R1],">0")+COUNTIFS(League1[Away],Calculations!C3,League1[R1],"<0")
F3:F14	F3	=COUNTIFS(League1[Away],Calculations!C3,League1[R1],"=0")+COUNTIFS(League1[Home],Calculations!C3,League1[R1],"=0")
G3:G14	G3	=D3-E3-F3
H3:H14	H3	=SUMIFS(League1[H],League1[Home],Calculations!C3)+SUMIFS(League1[A],League1[Away],Calculations!C3)
I3:I14	I3	=SUMIFS(League1[A],League1[Home],Calculations!C3)+SUMIFS(League1[H],League1[Away],Calculations!C3)
J3:J14	J3	=H3-I3
K3:K14	K3	=SUM(E3*3)+(F3*1)
L3:L14	L3	=J3*0.01
M3:M14	M3	=I3*0.001
N3:N14	N3	=SUM(K3:M3)
O3:O14	O3	=RANK.EQ(N3,$N$3:$N$14,0)
Dynamic array formulas.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: https://www.myonlinetraininghub.com/excel-forum/excel/dynamic-football-crests
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[ExceLoki]

try this
------------

Dynamic Images in League Table.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	position	teams	badge	teams	p	w	d	l	gf	ga	gd	pts
2	1	Melbourne City		Melbourne City	16	8	5	3	33	23	10	29
3	2	Western Utd		Western Utd	14	9	2	3	17	11	6	29
4	3	Macarthur FC		Macarthur FC	13	6	4	3	21	19	2	22
5	4	Adelaide Utd		Adelaide Utd	15	5	6	4	23	20	3	21
6	5	Melbourne Victory		Melbourne Victory	12	5	3	4	16	15	1	18
7	6	Sydney FC		Sydney FC	15	4	6	5	19	19	0	18
8	7	Wellington Phoenix		Wellington Phoenix	12	5	3	4	15	18	-3	18
9	8	Newcastle Jets		Newcastle Jets	12	4	3	5	20	21	-1	15
10	9	Perth Glory		Perth Glory	12	3	3	6	11	12	-1	12
11	10	WS Wanderers		WS Wanderers	13	3	3	7	13	20	-7	12
12	11	Central C. Mariners		Central C. Mariners	12	3	2	7	17	20	-3	11
13	12	Brisbane Roar		Brisbane Roar	12	3	2	7	11	18	-7	11
Standings

Cell Formulas
Range	Formula
D2:D13	D2	=INDEX(Calculations!C$3:C$14,MATCH(A2,Calculations!O$3:O$14,0))
E2:L13	E2	=VLOOKUP($D2,Calculations!$C$3:$K$15,COLUMN()-3,FALSE)
B2:B13	B2	=Standings!$D2