Dynamic Chart Plotting

[arunraman]

I have an excel sheet which has monthly data for 3 years and have plotted them on a graph.i would like to select a From Date and a To Date were in i select the data range/period and the Graph changes accordingly incorporating the duration which has been entered in the From and To Date.


Thank you for your help.

Arun Raman

 

[Radu]

Hi. Maybe you could pick up some ideas from this: http://www.processtrends.com/pg_chart_scroll_bar_checkbox.htm or this: http://www.processtrends.com/pg_dynamic_charts.htm. Or, you could go with applying data filters to your source data, like here: http://chandoo.org/wp/2009/02/12/make-a-dynamic-chart-using-data-filters/.


Hope it helps,

Radu

 

[keymaster]

Agree with Radu... the easiest approach is to use data filters. They move the burden to excel and work well for almost any type of data (not just dates).


But if you must select to and from dates, here is the approach.


Capture the to and from dates in 2 cells like A1, B1


Now, make some named ranges using OFFSET formula. Examples here: http://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/


Use the named ranges when you are creating the chart and then you should be good to go...

 

[mswenoflb]

I followed the example and it worked fine using the offset formulae. My chart showed the correct range and excluded blank or zero rows. However, when I ran a macro (filter command) that replaced the data rows with new information, the chart displayed erroneous data. I checked all references in my named ranges and they had not changed. However, the range in the Chart Data now showed differenct ranges for the data series values.


Any ideas as to why this is happening? BTW, the list filter wouldwork if I unchecked the zero value selection.