Dynamically Remove a Series on a Chart Based on Cell Value

[Bobbbo]

I have a situation that I need to not plot a data series if a cell value "Benchmark=NO" is NO. I have attached a workbook that has two tabs one for "Data" and one for Charting the data "Chart". The first chart on the Chart tab shows how all the data is normally plotted. If Cell "D10" is "NO" then I do not want the "Benchmark" data plotted automatically. The desired result is the second chart where "Benchmark" is removed.

Does anyone know how to do this?

[sandy666]

maybe this: =IF($D$10<>"NO",$T1,"")

[Bobbbo]

I have to apologize, my example makes no sense, as it had formulas in it that resulted in all the values being zero. Here is a new example.

[sandy666]

Benchmark is hardcoded and I think it shouldn't because this simple formula will not be flexible but you can do what you want of course

[sandy666]

You can use this version and change Benchmark in BenchmarkValues then use Yes or No from D10

[Bobbbo]

Benchmark is hardcoded and I think it shouldn't because this simple formula will not be flexible but you can do what you want of course

Thanks sandy666, I tried your solution but it still plots the "benchmark" when the formula is not "Yes". It plots it's value as zero (0) but does not remove the "benchmark" from the legend. Making the value a blank does not work. Thanks anyway!!

[sandy666]

You chart from second example doesn't contain data from benchmark
also turn on Automatic Calculation

automatic.jpg

I think you need create chart again with Benchmark then use Yes/No

so try this:

[Bobbbo]

You chart from second example doesn't contain data from benchmark
also turn on Automatic Calculation

Attachment 556025

I think you need create chart again with Benchmark then use Yes/No

so try this:

The legend still shows the endesired red line but now it says #N/A I need that series completely gone. Thanks again.

[Bobbbo]

You chart from second example doesn't contain data from benchmark
also turn on Automatic Calculation

Attachment 556025

I think you need create chart again with Benchmark then use Yes/No

so try this:

I still get the legend with the undesired "red" line now labeled "#N/A". see attached picture.

[sandy666]

in C5 : =IFERROR(CONCATENATE("Benchmark"," (",TEXT(D5,"0.0%"),")"),"")
but red line still will be there. It can be accepted?

or maybe it will help: Dynamic Excel Charts

[Bobbbo]

Hi sandy666, I am still struggling with this issue. Your last solution works but as you stated the Red Line is still in the legend. If there was a way to change the color of the line to White dynamically, the line would not be seen. Any ideas?

[sandy666]

Maybe with VBA
without it you can simply change border color to no line and font to white or set transparency to 100% for both - text and line, but.... it will not be visible whatever you select Yes/No

did you read Peltiertech link from post #10?

[Bobbbo]

Yes I did read the Link, and it did not make too much sense to me, so it was not helpful in my case

[JeteMc]

Perhaps this will be of some help. Working with the 'Normal' chart in the file Sandy666 attached to post #7 and the IFERROR addition to the formula in post #10, consider the following:
Delete Benchmark... from the chart ledgend
Select the first (or last) data point in the line graph for the benchmark and select 'Add data label'
Immediately select that same data point again and select 'Format data label' then uncheck 'Value' and check 'Series name'
Note that when cell D10 on the data sheet is changed to 'NO' both the line graph and series name disappear.
Let us know if you have any questions.