Chart - Dynamic series

[Portucale]

Hi,

I have a chart with a 13 weeks trend, but it can have 1, 2 or 3 series according to the user choice,l how can I dynamically just show 1, 2 or the full 3 series? as to show 1, 2 or 3 series in the legend.

Thanks in advance,

 

[Debraj]

Hi Portucale..

Thanks to Excel 2013.. it almost take care of every customize in a single place..

http://www.techrepublic.com/blog/10-things/10-cool-new-charting-features-in-excel-2013/

However in case of prior 2013 excel version,
You have to use some formula based approach to dynamically display only selected rows..

Here is a post by Theodor

and in case if you are looking for VBA based approach.. check this one too..

http://chandoo.org/forum/threads/dynamic-date-range-charting-vba.14094/#post-83794

 

[Portucale]

Hi Debraj,

This looks great, but not sure if I don't understand it, or it will not fit in what I am looking for, I enclose an example of my data, where you can see the user ticks a check box or multiple check boxes, and a maximum of three series can be displayed, my challenge is when the user just tick one or two check boxes.

Any help, and, all help is very much appreciated.

Cheers,

 

[Debraj]

WOW.. its almost working fine..

Still I have some confusion..

• are you talking about SUMMARY sheet or Sheet 3
• If Sheet 3, then its working fine.
• are you asking why.. Series is there, at the bottom laying when there was no DATA..
• Then you will be happy to know that.. CHART can ignore #N/A but it consider "" as value.
  • Select B34 : N36
  • Find : ,"",
    
  • Replace : ,NA(),

BTW, if you are not happy with that.. You can use DYNAMIC NAME RANGE for chart as your data is ready to do this trick.

 

[Portucale]

Thanks Debraj, all is working I just can't remove the empty series from the legend... any idea?

 

[Faseeh]

@Portucale,

Goto select data, Select the blank series and press delete..done.

 

[Portucale]

Cheers Faseeh, but this is a dynamic chart which can have 1, 2 or 3 series...