dynamic chart with no label for label at 0

[cidfidou]

Hi guys,

i am trying to create a dynamic chart with dynamic series.

As an example lets take a list of users for which a specific quantity is assigned per week. We only want to plot the user for which the total quantity assigned is >0 across two weeks let say

Data source (below table starting in D1)

	week1	week2	Total
User1	0	0	0
User2	0	0	0
User3	3	0	3
User4	0	0	0
User5	5	0	5
User6	0	0	0
User7	0	0	0
User8	0	0	0
User9	0	0	0
User10	7	0	7

<tbody>
</tbody>

Data sorted from datasource (below table starting in I1)

	week1	week2
User3	3	0
User5	5	0
User10	7	0
	#N/A	#N/A
	#N/A	#N/A
	#N/A	#N/A
	#N/A	#N/A
	#N/A	#N/A
	#N/A	#N/A
	#N/A	#N/A

<tbody>
</tbody>

I have used below formula in above table to only show user 3 ,5 and 10 as their total is > 0

I2 = {IFERROR(INDEX($D$2:$D$16;SMALL(IF($G$2:$G$16>0;ROW($G$2:$G$16));ROWS(I$2:I2))-ROW($G$2)+1);"")}
J2=VLOOKUP($I2;$D$2:$F$11;2;FALSE)
k2=VLOOKUP($I2;$D$2:$F$11;3;FALSE)


and then I have used some dynamic range formula to plot above table

user = =$I$2:INDEX($I$2:$I$16;COUNTIF($I$2:$I$16;">"""))
week1 =$J$2:INDEX($J$2:$J$16;COUNTIF($I$2:$I$16;">"""))
week 2 = =$K$2:INDEX($K$2:$K$16;COUNTIF($I$2:$I$16;">"""))


So my question is, how can we remove a data label from legend when there is no value. In my example I would like to remove week 2 showing in the legend of the Chart and from the bottom of the chart as it is taking some space for nothin? the issue is that I could have at least 10 weeks and dont want see in them in the chart...


Hope this is clear