Removing a legend entry on a chart

HI again

Was hoping someone could help me out again, trying to get numerous charts working from the same data sources.

In the attachment, i am have set up a Yes No dropdown to show the series i want to display. Only problem is I don't want a series that doesn't appear in the chart to appear in the legend. (series B in the example).

Can anyone help?

Thanks

The only way to automatically remove the legend entry is by removing the series.

If Series B is set to "No" should the gap between A and C be held in the chart?

Making Series dynamic is a pain.

One way would be to use a pivot table/chart. Although it requires a change of data layout.

Otherwise VBA code would be needed to add/remove series and adjust range used.

q felt,

Having Andy look this over is kind of like getting batting tips from Babe Ruth (forgive the American reference, but I don't know any famous Cricket players)...but I took a modest poke at it and replaced =IF(...,...,"") with =IF(...,...,NA()). It holds the place for the series just fine, and it looks like it wants to do what you want with the legend, but can't quite get there. You'll see what I mean in the attachment.

Andy, can you figure why the legend behaves differently, depending on which series becomes "invisible"?

Hi Ryan,

You are correct that the column gaps are held when using #NA, or "". Unfortunately q felt said, 'No I want thegap to go as well.'

For a column chart #NA and "" act the same, assuming the axis crossing point is zero.

The only way to get the legend and column gaps to work is to remove the series.
You can do this via code or via hiding cells.

If you set the chart data to use range H4:J8 and use event code on the data validation cells B3:D3 then this will work.

Please Login or Register  to view this content.

Depends how 'polished' you need it to look. You can achieve similar by grouping the individual columns for each series (i.e. each column is one group). You can then use the little plus to pop the series in and out by hiding and unhiding the appropriate column(s). In order to stop the columns merging in to one mega group, you would need to separate them by one column each - which you couldn't hide utterly as it defines the width of the button to unhide the adjacent hidden grouped column.

Just another way - less pretty, but no code.

HTH