Chart: only show legend elements with values

[Brython]

Hi guys
I have 10 graphs with more than 20 legend entries. However, each graph only needs 3-4 elements out of the 20 legend entries in the graph.
Is there any way to force Excel to only show those legend entries that have a value? (without deleting them manually)
Thanks in advance!

 

[Andrew Poulsom]

In what way isn't it "playing ball"?

 

[RichCowell]

In what way isn't it "playing ball"?

I think I made a mistake initially, so it now looks like this:

{=IFERROR(INDEX(B$3:B$20,SMALL(IF($Y$3:$Y$20<>0,ROW(B$3:B$20)-ROW(B$3)+1),ROWS(X$3:X3))),"")}

but doesn't display anything, even for the rows that do contain information when copied down...

It won't matter that the data in column B (so B3) is the result of the following formula will it?

{=INDEX(ISP, MATCH(MIN(IF(ISBLANK(ISP)+COUNTIF(B$1:$B2, ISP), "", IF(ISNUMBER(ISP), COUNTIF(ISP, "<"&ISP), COUNTIF(ISP, "<"&ISP)+SUM(IF(ISNUMBER(ISP), 1, 0))+1))), IF(ISBLANK(ISP)+COUNTIF(B$1:$B2, ISP), "", IF(ISNUMBER(ISP), COUNTIF(ISP, "<"&ISP), COUNTIF(ISP, "<"&ISP)+SUM(IF(ISNUMBER(ISP), 1, 0))+1)), 0))}

 

[Andrew Poulsom]

I don't know why your formula isn't working without seeing your data. You can see that it works from my example in Post #8.

 

[RichCowell]

I don't know why your formula isn't working without seeing your data. You can see that it works from my example in Post #8.

I'm going to try to replicate your example in a new worksheet in my workbook and then edit it to fit my data...

Thanks,

Rick

 

[RichCowell]

Finally had chance to get this sorted, and it's working well now...

Just a quick further question...

in the B column where it's counting the results of A (doing it using a VLOOKUP), the results below the last cell with text in obviously display as #N/A

So I've now got:

P	Q
Option 1	10
Option 2	2
Option 3	4
	#N/A
	#N/A

<tbody>
</tbody>

With the formulae:

P	Q
{=IFERROR(INDEX(A$2:A$30,SMALL(IF($B$2:$B$30<>0,ROW(A$2:A$30)-ROW(A$2)+1),ROWS(P$2:P2))),"")}	=VLOOKUP(P2,ISP,2,FALSE)
{=IFERROR(INDEX(A$2:A$30,SMALL(IF($B$2:$B$30<>0,ROW(A$2:A$30)-ROW(A$2)+1),ROWS(P$2:P3))),"")}	=VLOOKUP(P3,ISP,2,FALSE)
{=IFERROR(INDEX(A$2:A$30,SMALL(IF($B$2:$B$30<>0,ROW(A$2:A$30)-ROW(A$2)+1),ROWS(P$2:P4))),"")}	=VLOOKUP(P4,ISP,2,FALSE)
{=IFERROR(INDEX(A$2:A$30,SMALL(IF($B$2:$B$30<>0,ROW(A$2:A$30)-ROW(A$2)+1),ROWS(P$2:P5))),"")}	=VLOOKUP(P5,ISP,2,FALSE)
{=IFERROR(INDEX(A$2:A$30,SMALL(IF($B$2:$B$30<>0,ROW(A$2:A$30)-ROW(A$2)+1),ROWS(P$2:P6))),"")}	=VLOOKUP(P6,ISP,2,FALSE)

<tbody>
</tbody>

I've tried:
=IF(ISBLANK(P2), "", VLOOKUP(P2,ISP,2,FALSE))

and

IF(VLOOKUP(P2,ISP,2,FALSE)=0, "", VLOOKUP(P2,ISP,2,FALSE))

but they still display #N/A

Is there a better way of doing that count, or a way of hiding the #NA's?

 

[Andrew Poulsom]

Maybe try:

=IF(P2="","",VLOOKUP(P2,ISP,2,FALSE))

or you could use IFERROR:

=IFERROR(VLOOKUP(P2,ISP,2,FALSE),"")

 

[RichCowell]

Maybe try:

=IF(P2="","",VLOOKUP(P2,ISP,2,FALSE))

or you could use IFERROR:

=IFERROR(VLOOKUP(P2,ISP,2,FALSE),"")

Both worked perfectly! Thanks a lot for all your help!!

 

[RichCowell]

Okay - last question in relation to the results of what you've helped me accomplish so far...

I've now got a table with the filtered results I need, with a potential total of 30 rows as the numbers & stats grow - I can create a pie chart from that obviously, but it includes the coloured squares in the legend for all the blank rows too... I've tried the options to "show empty cells as gaps" (which is the default) so tried it as Zero's just in case, but can't get them to go...

Is there any way it can just display items in the legend for cells that contain data?

 

[VoG]

This may help you AJP Excel Information - Exclude zero values from pie chart

 

[RichCowell]

This may help you AJP Excel Information - Exclude zero values from pie chart

I've had a look at that, but the data it's using doesn't include zero's...

It's not that my data contains zeros either, they just don't contain anything - in either column - I need to include them in the pie chart for when they do though, as the cells will populate automatically based on data in another spreadsheet... I'm going to have about 30 of these spreadsheets based on similar sets of data, and don't want to have to adjust the range of the source data every time it changes...