Auto Format Scatter Chart

[bluenose5709]

I wonder if you may help,

I have inserted a scatter chart based upon the data you can see in the image below, this is working fine, however what I would like now is to auto colour the dots based upon the classification, For example:

Striker = Gold
Midfield = Silver
Defence = Bronze
Winger = Blue

Any help will be hugely appreciated.

Thank you

 

[Kaper]

My idea would be to create 4 pairs of names in name manager (Ctrl+F3) using Filter. If your fata is in Excel table you can use structural references if not just absolute references. as presented below in columns N amd M resp.

The names (I prepared only 3 pairs) are listed in rows 13... (of course you don't keep these cells (nor formulas in N2 and M2) in ready sheet. It is just info. And note that my local separator in formula is semicolon, you probably need a comma.

Then you can use these names while adding series to a graph.

and the graph is:

The snipped of sheet and formulas made with XL2BB (highly recommended on this forum):

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Column1	Column2	class	Column3	Column4	age	Column5	Column6	time on squad	Column7	Column8		Striker	Striker
2			Winger			18			88				17	49
3			Defence			18			51				16	25
4			Midfield			18			61				16	37
5			Winger			18			57				16	29
6			Winger			17			36				15	21
7			Striker			17			49				15	25
8			Midfield			17			45				14	12
9			Winger			17			41
10			Defence			16			37
11			Midfield			16			45
12			Striker			16			25
13			Striker			16			37			Defence_age	=FILTER(Table2[age];Table2[class]="Defence")
14			Defence			16			33			Defence_ToS	=FILTER(Table2[time on squad];Table2[class]="Defence")
15			Striker			16			29			Midfield_age	=FILTER(Table2[age];Table2[class]="Midfield")
16			Winger			16			25			Midfield_ToS	=FILTER(Table2[time on squad];Table2[class]="Midfield")
17			Striker			15			21			Striker_age	=FILTER(Table2[age];Table2[class]="Striker")
18			Winger			15			29			Striker_ToS	=FILTER(Table2[time on squad];Table2[class]="Striker")
19			Striker			15			25
20			Defence			14			21
21			Striker			14			12
22			Defence			14			14
23			Midfield			13			7
24			Winger			13			12
25			Defence			13			6
26			Midfield			13			8
27
Sheet1

Cell Formulas
Range	Formula
M2:M8	M2	=FILTER(Table2[age],Table2[class]=M1)
N2:N8	N2	=FILTER($I$2:$I$26,$C$2:$C$26=N1)
Dynamic array formulas.