How to cook a delicious dynamic chart that will have your boss drool

[GN0001]

Chandoo has posted some knowledge on how to maket dynamic charts:


This the link:

http://chandoo.org/wp/2010/08/31/dynamic-chart-with-check-boxes/


I followed all the steps, but when I uncheck the control boxes, only the legend is showing as NA, the column of data stays there.


For example: I want not to show the profit, I unchecked the profit unbox and then the legend name came as NA, but the data for profit still stays there.


Any help?


Please look at this


Year Sales Profit No. of Customers

2003 1515 145 48

2004 1526 1174 391

2005 1444 524 262

2006 1562 331 110

2007 1490 873 873

2008 1571 318 159

2009 1702 619 206


Status TRUE TRUE TRUE


Sales Profit No. of Customers

1515 145 48

1526 1174 391

1444 524 262

1562 331 110

1490 873 873

1571 318 159

1702 619 206


I can't copy the chart and control boxes here.

What chandoo has done that when he uncheck the check box, the whole date is not being shown?

Regards

Guity

 

[Luke M]

Make sure that your IF formulas control your data, not (necessarily) your series label. (Step 4 in Chandoo's example)

 

[GN0001]

This is my formula:

=IF(C$13,C4,NA())

When I enter the range (C4:C11) instead of column label C4, it returns #Value!

What do I need to enter to select the whole range instead of column label?

Thank you for the help.

Guity

 

[Luke M]

Don't enter the whole range. As you copy down, the formula will change to C5, C6, C7, etc.


Essentially, you have your original data somewhere, and then you have a column of data that references the original data. This lookup data is what the chart is based on. The formula acts like a switch, either displaying the lookup data or NA.

 

[GN0001]

Would you send me what the formula is like for true part of If function? I can't figure it out?

I did something else and that worked for me. Do you anything else that helps me? Instead of only one cell in true condition of "If function": I entered: =offset(C$,0, 0, 7,1) and then I hit CTRL+Shift+Enter.

Regards,

Guity

 

[Luke M]

Let's say the data you want in your graph is in cells C3:C11, with C3 being the title of series.


C13 is the cell linked to checkbox

In C14, put:

=IF(C$13,C4,NA())


Copy this down to D10. Add D2:D10 to your graph. If D1 is true, then D2:D10 will look exaqctly like A2:A10, else it will all just show #N/A. You are making your formulas too complicated - each cell only returns 1 other cell.