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]

Use the following name definitions:

ISP_PriLab =OFFSET(ISP!$P$1,1,0,COUNTIF(ISP!$P$2:$P$25,"?*"),1)
ISP_PriVal =OFFSET(ISP!ISP_PriLab,0,1)

 

[RichCowell]

Awesome! Thank you so much!!

Now just to try to understand those definitions and formulae so I know what they mean rather than just adapting them each time!

Thanks again - I've been able to do exactly what I wanted to do now, which was get the result of loads of pivot charts without using pivot tables...

 

[Andrew Poulsom]

Looking again you could also have changed the definition of ISP_PriVal from:

=OFFSET(ISP!$Q$1,1,0,COUNTA(ISP!$Q:$Q)-1,1)

to:

=OFFSET(ISP!$Q$1,1,0,COUNT(ISP!$Q:$Q),1)

 

[RichCowell]

Thanks, I'll check that out too

 

[RichCowell]

Okay, this isn't related to the same topic, but it's in the same workbook so working with the same data, and you've been so helpful so far - thinking this will be a quick one, but can't think how to phrase it concisely enough to get anything on Google...

I need to do a line graph with a line of best fit - I've done the main one, which works fine obviously...

I need to do one that only counts the value if another cell contains "AAA" or "BBB" etc.


e.g.

	A	B	C	D	E
1	School Type	Devices	Staff
2	Primary	20	5
3	Secondary	500	50
4	Secondary	1200	70
5	Primary	50	12

<tbody>
</tbody>

So I'm going to do separate Line Graphs for the different school types... but want to keep them completely separate...
I don't mind recreating the table - I've done that with similar data, but listing unique values, and that's not what I want this time...

 

[Andrew Poulsom]

I don't see any AAA's or BBB's in your sample data.

 

[RichCowell]

Sorry, I should have used that instead of Primary/Secondary...

 

[Andrew Poulsom]

You can make lists using a formula like the one in Post #8, testing column A for Primary or Secondary.

 

[RichCowell]

Yep, I definitely need to understand them more so I can manipulate them easier when necessary...

I think I've done it though, the data isn't quite laid out like it is in the table above, so I've used:

=IFERROR(INDEX(G$2:G$500,SMALL(IF($B$2:$B$500="Primary",ROW(G$2:G$500)-ROW(G$2)+1),ROWS(O$2:O2))),"")

To get the filtered list of IT Staff, and then changed it slightly to get the corresponding devices for those results:

=IFERROR(INDEX(J$2:J$500,SMALL(IF($B$2:$B$500="Primary",ROW(J$2:J$500)-ROW(J$2)+1),ROWS(P$2:P2))),"")

Seems to be working perfectly, thank you!! (again!)

 

[RichCowell]

Final question I think...

Is it possible to add an "OR" in where it's looking for ="Primary" at the moment? So if the total options were primary / secondary / college, could I get it to give me the results for those which are Primary and Secondary ="Primary"OR"Secondary"? (I've tried the OR like that and it doesn't work...)