Choose chart start and end date from drop down

[steeful]

Hello,

I am trying to create a dynamic chart where the user can select the start and end month. I am not sure about how to go about this, but I assume it involves named ranges. Any pointers or tips are greatly appreciated.

Thanks in advance

 

[Derek Brown]

Have a look at Jon Peltier's excellent site and you will see a section titled "Dynamic Charts" in the index:
http://peltiertech.com/Excel/Charts/ChartIndex.html
Here you will find many examples and files that can be downloaded.
If you have any further questions it would be useful to know which version of Excel is being used.

 

[steeful]

Thanks.

I have gotten this far with a code:

=OFFSET('Mstar Data'!$C$2,MATCH(StartDate,ChartDates,0)-1,0,MATCH(EndDate,ChartDates,0)-MATCH(StartDate,ChartDates,0)+1,1)

But unfortunately it only works for dates that are in a column (going down) whereas I need it to work going across a row. How would I go about doing this? The name "ChartDates" has selected all the dates in the row. "StartDate" and "EndDate" are user dropdowns with data validation lists referring to "ChartDates".

Thank you very much!

 

[steeful]

Okay, I figured out how to get it to do it by row (across) with the following code:

=OFFSET('Mstar Data'!$A$2,13,MATCH(StartDate,ChartDates,0)-1,,MATCH(EndDate,ChartDates,0)-MATCH(StartDate,ChartDates,0)+1)

But it is starting two cells later (in terms of start date, two months) later than the month referenced in the StartDate dropdown. Any ideas? Thanks in advance!

 

[Derek Brown]

It is difficult to say without knowing the column range for 'ChartDates'.
If you use 'Evaluate Formula' you should be able to see how the formula is working. It will be found on the 'Formulas' TAB if you are using Excel 2007/2010.

 

[steeful]

The range for "ChartDates" starts at D2 and runs through EA2. I currently have EndDate as Jun'11 (D2) and StartDate as Jan'10 (U2), but the chart is a leaving off Jan'10 and starting at Feb'10 (T2) instead. What am I doing wrong here? Here is the formula (I've been tinkering, trying to get it match the dates):

=OFFSET('Mstar Data'!$B$2,9,MATCH(StartDate,ChartDates,0)+1,,MATCH(EndDate,ChartDates,0)-MATCH(StartDate,ChartDates,0))

Many thanks!

 

[Derek Brown]

Probably worth checking the 'Refers To' for the name ChartData to ensure that it is starting in D2. You could also check this by selecting the name in the 'Name Box', unless you have that as a dynamic formula.

 

[RichardMGreen]

This might be worth a look as well.
http://www.databison.com/index.php/stock-chart-with-scroll-and-zoom/

When set up correctly (there's a small error in one of the formulas) it allows you to scroll through a chart and zoom in to various portions.

Looks good when sent to managers as well.