hide legend for #N/A series
 

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

hide legend for #N/A series
 

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?

--
Thanks,
Shane Devenshire


"Ernst Schuurman" wrote:

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

hide legend for #N/A series
 

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"ShaneDevenshire" wrote in
message ...
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?

--
Thanks,
Shane Devenshire


"Ernst Schuurman" wrote:

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

hide legend for #N/A series
 

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


"Jon Peltier" schreef in bericht
...
Shane's correct, this cannot be handled automatically without VBA.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"ShaneDevenshire" wrote in
message ...
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?

--
Thanks,
Shane Devenshire


"Ernst Schuurman" wrote:

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