Chart Between Two Dates

[wporter86]

Hi There,

I have a data series that consists of Dates in column A and values in Column B.

I would like to be able to put a start date in cell C1 and an end date in Cell c2, and have a graph that would automatically update to only plot the values between these two dates?

Would anyone have any insight into how this might work? I assume some VBA code might be necessary but happy for a simpler solution if there are any suggestions?

Regards,

[6StringJazzer]

There are at least a couple of ways to do this without VBA. You will get better answers if you attach your file (see below).

One way is to create dynamic named ranges for the values from the desired start to end dates. I have not tested this, would be happy to do so if you provide your file:

ChartDates:

=INDEX(Sheet1!A:A,MATCH(Sheet1!C1,Sheet1!A:A,0),1):INDEX(Sheet1!A:A,MATCH(Sheet1!C2,Sheet1!A:A,0),1)

ChartValues:

=INDEX(Sheet1!B:B,MATCH(Sheet1!C1,Sheet1!A:A,0),1):INDEX(Sheet1!B:B,MATCH(Sheet1!C2,Sheet1!A:A,0),1)

Then use ChartDates and ChartValues to define your chart ranges.

Another way is to use "helper" columns that have formulas to include only the dates you're interested in. Easier to show you in your file than to write out a description.


The paper clip icon does not work for attachments. Instead, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

[wporter86]

Hi,

Thank you so much for your prompt response.

I will work on the named ranges solution you have suggested, but have had trouble with this approach so far. This would be the preference as I intend on having the dates change with a formula.

I have attached a sample of the file for you which may help you to explain where i am going wrong!

Thanks,

[JeteMc]

Another possible solution would be to use a pivot table and pivot chart as modeled on sheet 2 of the attached file. The date filter at the bottom of the pivot chart could be used to determine the dates between which the data would be chosen, as opposed to using cells F3:F4 on sheet 1.
Let us know if you have any questions.