Define chart data range from user input (Start & End)

[arlinsandbulte]

I've tried a bunch of tutorials and how-to's, but I keep getting errors.

Goal: set the data range of a chart based on user input.
In my example, I am charting some historical data with date on the horizontal (x) axis with a line chart.
I want the user to type in a date value (cell B1 in my example) & a Duration (cell B2 in my example).
The chart should display the data defined by the entered date range.

Based on some online tutorials, I am trying to use the NAMED RANGES and the OFFSET function to dynamically define the chart range. For example, applying Method 1 from here from the Microsoft help site.
But, despite the fact that I KNOW my named range formula is working, I get an error when I input the named range into the chart data source fields: "Excel found a problem with one or more formula references in this worksheet...."

Most of the examples and tutorials I have found only dynamically change only the *END* of the chart data range, but I am also dynamically updating the *START* of the chart data range too.

Here is the formula I am using in the named range definition:
=OFFSET(INDIRECT(CONCATENATE("E",MATCH(Plot_End_Date,Sheet1!$E:$E))),0,1,-1*Plot_Duration,1)

Here is a screenshot of my Excel Document: https://imgur.com/a/Y5gds
And here is a screenshot of the error I get: https://imgur.com/a/K6GDS

You can download the spreadsheet I am using to test this out here:
https://www.dropbox.com/s/o47cplmtiigkws0/range test.xlsx?dl=1

Thanks for any help you can provide!

 

[codeliftsleep]

I've always found it easier to put the data I want to use in a table and then set the chart to the table...it will automatically update itself when you add/delete rows/columns

 

[arlinsandbulte]

codeliftsleep: Yes, but I don't want to delete or add data to the sheet. I just want to specify a range to plot within a large subset of data. So, that does not work in this case.

 

[arlinsandbulte]

Got it!
I'm not 100% sure what I was doing wrong, but I think it was one of two things (or both):
1.) I was prefixing the named range with Sheet1! instead of Workbook.xlsx! in the chart data reference.
2.) I changed the OFFSET reference value to $A$1 instead of using a fancy formuala to get that reference to the start of the data. Inststead, I just use the rows & cols formula parameter values.

So, my OFFSET formulas became these:
[TABLE="width: 641"]
<tbody>[TR]
[TD="class: xl63, width: 641, colspan: 9"]=OFFSET(Sheet1!$A$1,MATCH(Plot_Start_Date-1,Sheet1!$E:$E),5,Plot_Duration,1)
&
[TABLE="width: 641"]
<tbody>[TR]
[TD="class: xl65, width: 641, colspan: 9"]=OFFSET(Sheet1!$A$1,MATCH(Plot_Start_Date-1,Sheet1!$E:$E),4,Plot_Duration,1)

And it worked!

I'm all good now.
I updated the excel file hosted here with the fix.... but it won't stay there forever.[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]