Dynamic Graph when I change date input

[randomwalker]

Hi,

Coulme A are dates starting from 2007/2/1, Column B are the close prices corresponding to the dates in Column A for stock T, Column C are turnovers corresponding to the dates for stock T. I use Excel to plot a graph of which the X axis is date, primary Y axis is stock price and secondly Y axis is the turnover.

cell D1 is the beginning date I input and cell F1 is the ending date I input so that a date range is formed. What I want to achieve is the graph chanes automatically when I change the dates in both D1 and F1.

First, I use functions cell, index and match to find out the reference of the beginning date and the ending date in cell D2 and F2. For example, I input 2007/2/2 in D1, and 2007/2/16 in F1, above combined functions will result $A$2 and $A$12.

In E1, I input =row(indirect(f2))-row(indirect(d2)) to get the # of rows between 2007/2/2 and 2007/2/16.

Go back to the graph, in Source Data..., I tried to change Category (X) axis labels to =offset(indirect(sheet1!D2),0,0,sheet1!E1+1,1). But it doesn't work because Excel pops up an error message.

Then I tried to use the name range with the same formula =offset(indirect(sheet1!d2),0,0,sheet1!E1+1,1). It doesn't work either.

Could any one here help?

Thanks so much in advance!

 

[Richard Schollar]

Hi

I can highly recommend the following article by Jon Peltier:

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=246

It will take you thru the process and should help you sort out your problem.