Charts With Dynamic Date Ranges And Multiple Series

[johome]

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:

 =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):

='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!

 

[johome]

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?

 

[johome]

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!