Fixing bar colours based on product

[Zubair]

Hi all,

I have made the bar chart linked to the table and changed each bars colour based on the product colour F4:G7 and saved chart as a template.

The chart is automatically showing bars on descending order based on the sales value of the product.

The issue I am facing is, when product sales value changed the bar colour is not showing colours based on product i.e. F4:G7, else colours have been fixed based on decending order of the sales value.

Given below the XL2bb Mini Sheet and screen shot of the graphs.

View 1 is the standard I fixed bar colours and saved the template.
View 2 I am changing sales value of A from 1000 to 500 and I was expecting A colour bar to appearing with green colour whereas its showing blue and B with green.

Can anybody please help me to fix the bar colour based on product rather then sales value ?

Mr. Excel.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	VIEW 1									VIEW 2
2						Colour	Product	Sales							Colour	Product	Sales
3
4							A	1,000								A	500
5							B	900								B	900
6							C	800								C	800
7							D	600								D	600
8
9
10
11							Total									Total
12							Column1	Column2								Column1	Column2
13							A	1,000								B	900
14							B	900								C	800
15							C	800								D	600
16							D	600								A	500
17
18
19						How to fix bar colour based on the product									How to fix bar colour based on the product
20
21
22
23
Colour fixing bar

Cell Formulas
Range	Formula
G13:H16	G13	=IFERROR(INDEX(G$4:G$7,MATCH(LARGE($H$4:$H$7,ROWS(G$13:G13)),$H$4:$H$7,0)),"")
P13:Q16	P13	=IFERROR(INDEX(P$4:P$7,MATCH(LARGE($Q$4:$Q$7,ROWS(P$13:P13)),$Q$4:$Q$7,0)),"")