dynamic date range for scatterplot chart

[sanlejushi]

Dear excel experts

for historical analysis and plotting trends, i do quite abit of charts plotting historical data, comparing different time series over particular time periods

However, each time i want to change the period i am interested in, i have to go to the X axis scale to manually key in the maximum and minimum values for X axis.

May i enquire if anyone has a faster way of changing dates, e.g such as using comboboxes to pick the starting and ending date ...not sure if this is possible, thanks.

or does anyone has a more convienient way of doing this...many thanks.

 

[MarkAndrews]

I import the data using MS Query & date Parameters (from a hidden sheet), then bounce the chart title from the dates (filter dates - user selected)

A2 & B2 being the user selected dates

EG -

="Period Selected : " & TEXT(A2, "dd mmmm yyyy") &" - " & TEXT(B2, "dd mmmm yyyy")

As the dates change, so does the chart & the chart title

 

[clayv]

If you use named ranges you can do this by update the min and max values in 2 cells. But the named range formula is pretty complicated (e.g. OFFSET(Sheet1!$A$3,MATCH(Sheet1!$E$1,Sheet1!$A$3:$A$13,0)-1,0,MATCH(Sheet1!$E$2,Sheet1!$A$3:$A$13,0)-MATCH(Sheet1!$E$1,Sheet1!$A$3:$A$13,0)+1,1) )

So, if you'd like a sample workbook PM me and I can send one your way.