hide legend for #N/A series

[Ernst Schuurman]

Hi, Chart guru's,

I have created a spreadsheet with multiple columns that use formulas for
getting the chartdata.
Depending on a datavalidationlist different charts can be made within only
one chartobject.
Sometimes this results in a chart with less series than the maximum.
For this purpose I have put an If condition in my formula that causes the
formula result to become #N/A()
That works fine for the series, but in my legend the color for that series
remains, giving #N/A as the description.

Does anyone of you know a sollution for this?

Thanks in advance,

Ernst Schuurman

 

[ShaneDevenshire]

Hi Ernst,

If I understand correctly you want the legend entry to disappear when there
is no data for the series? If that is what you want I believe you will need
to use VBA, is that acceptable?

 

[Jon Peltier]

Shane's correct, this cannot be handled automatically without VBA.

- Jon

 

[Ernst Schuurman]

Shane, Jon,

Thanks for your anwsers,

I used to do this entire solution in VBA, but tried to do this all with
worksheetfunctions.
So far I succeded, but got stuck on this.
I will add a small piece of code to hide the legends if needed.

Kind regards,

Ernst