Excel Chat

[vmjan02]

this is the chat data from P2 to Q25 the numbers are all calculated. as per the screen shot the chart is not working as dynamic range any soultion then sheet name is table.
should exclude blank from x axis

FTTH Technial Complaints Weekly Report V1.xlsm
	P	Q
1	sub
2	No Internet connection	4434
3	All services not working	3146
4	Loss of signal	1668
5	OLO ONT Down	1320
6	Slow Browsing	1154
7	Modem Faulty	783
8	No browsing	631
9	Browsing intermittent	583
10	Baity Wi-Fi Mesh	454
11	No dial tone	262
12	STB Faulty	220
13	Telephone& Internet problem	122
14
15
16
17
18
19
20
21
22
23
24
25
Table

Cell Formulas
Range	Formula
P2:P25	P2	=IFERROR(INDEX($L$2:$N$54,MATCH(Q2,$N$2:$N$54,0),1),"")
Q2:Q25	Q2	=IFERROR(INDEX($N$2:$N$65,MATCH(0,IF($N$2:$N$65<>0,COUNTIF(Q$1:Q1,$N$2:$N$65),1),0)),0)
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
sub_clasifiction	=OFFSET(Table!$Q$2,0,0,COUNT(Table!$Q$2:$Q$65),1)	Q3:Q25, P2

 

[Legacy 143009]

Have you tried to use Pivot Chart?

 

[RoryA]

What range are you using for the category axis values? If you use a dynamic range like =OFFSET(sub_clasifiction,0,-1) it should only include cells where there is a number in column Q.

 

[vmjan02]

What range are you using for the category axis values? If you use a dynamic range like =OFFSET(sub_clasifiction,0,-1) it should only include cells where there is a number in column Q.

yes using offset

 

[vmjan02]

Have you tried to use Pivot Chart?

no pivot chat is not the need

 

[RoryA]

yes using offset

I just noticed that your value formulas are returning 0 not "" so presumably you have set the sheet or range to not display 0 values. In that case, your COUNT will include all the cells. You may have to use something like COUNTIF($P$2:$P$65,"?*") instead.

 

[vmjan02]

ok got the idea, i will change the formual to values, then it will work, thanks a ton man.