Charts With Dynamic Date Ranges And Multiple Series

johome

New Member
Joined
Oct 19, 2015
Messages
5
Goal: Dynamically update the date range and title of multiple charts (around 20, each with between 2 and 5 series) based on a drop-down selection.

Problems:
  1. My named range ("MonthReportRange") works when input into a chart's data range, but instead of remaining dynamic, it instantly converts the range to the absolute addresses of the start/end dates of whichever drop-down menu option is selected.
  2. I need a general formula to apply to each data series. It is impractical to create named ranges for each series on each chart.

Where I am:
The charts currently update their data dynamically based on the last date entered in a column. Now I'm trying to get them to also change their start date based on the drop-down selection I make. I generate these charts in numerous dated sheets: 7 days, 30 days, 180 days, and 365 days, and they populate a Report sheet. The dated sheets pull the most recent 7, 30, etc. sets of data from my database. Right now, I'm just dealing with the 30-day report
I have been able to find and dynamically change the start date's location for MonthReportRange (the drop-down selections are 30 days, 28 days, and this month); The end date is static (it will always be in cell A42).

Here is the code I am using to create MonthReportRange, with the nested named ranges expanded below:
Code:
 =MonthStartDate:'30 days'!$A$42
 =INDEX(MonthAllDays,MATCH(OFFSET(MonthChosenOptionNumber,0,2),MonthAllDays,0)):'30 days'!$A$42
 =INDEX('30 days'!$A$5:$A$42,MATCH(OFFSET(INDEX('30 days'!$BJ$12:$BJ$14,MATCH('30 days'!$BL$10,'30 days'!$BJ$12:$BJ$14,0)),0,2),'30 days'!$A$5:$A$42,0)):'30 days'!$A$42

And that's as far as I got. Putting MonthReportRange into a chart's data range field works as described above, freezing the range in place even after the drop-down is changed.

In terms of a general formula for the data series, there are several tutorials that I've found that demonstrate a dynamic date range, but they include only one series besides the date, and they use a specific named range for that series. Because of the number of series I'm tracking, creating a named range for each is impractical for me. I tried to take the MonthReportRange and remove a few crucial $, but that hasn't produced the result I'm looking for in the chart (though it does call the correct data, and does drag across correctly if you take the back half of the range off):

Code:
='30 days'!$A$4,'30 days'!monthreportrange,'30 days'!$X$4,INDEX('30 days'!A$5:A$42,MATCH(OFFSET(INDEX('30 days'!$BJ$12:$BJ$14,MATCH('30 days'!$BL$10,'30 days'!$BJ$12:$BJ$14,0)),0,2),'30 days'!$A$5:$A$42,0)):'30 days'!A$42

Any advice would be greatly appreciated, as I've been working on this problem for 2 days now and am simply stuck. I feel that I've solved most of the problems, but graphing the results is proving too tricky.

Thanks so much!
 
Last edited:

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
I tried to make that as clear as possible, but maybe it's too complicated to answer. So let me simplify the question:

How can I dynamically change the start date of a chart without creating a named range for each data series in the chart?
 
Upvote 0
I'm sorry to bump this again, but I could really use some assistance. The reason I'm resistant to using named ranges is for customizability; each member of my team will be tracking different data sets, and those sets can and will change over time. This means that a general formula is all but required, unless I'm going to personally customize each person's named ranges every time something changes (spoiler alert: I'm not).

I was considering using data filters, but that doesn't actually change the number of items represented in the charts, which is what I'm trying to do with this drop-down option; I'm currently using a combo box.

Thanks again for your help!
 
Upvote 0

Forum statistics

Threads
1,224,223
Messages
6,177,260
Members
452,765
Latest member
Erka Gizli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top