Variable data range in chart

[F6Hawk]

I have a chart that uses years as part of the data range. I want one of the data series to always start with 2019, but end at a year specified in another formula.

Currently, that data series has the Y-axis values set by: =SenCalculator!$E$4:$E$45
I want it to look like: =SenCalculator!$E$4cell value specified by formula)

I have tried using =CELL("address",INDEX($E$4:$E$45,MATCH(O31,$A$4:$A$45,1))) which returns the proper cell address (let's say $E$16) in the sheet, but cannot get it to work in the data source in the chart.

I have tried defining names such as BeginEOY and EndEOY which again, show up properly in the sheet (=EndEOY shows up as $E$16), but cannot figure out how to use them as limits in the data source in the chart.

Surely there is something simple I am missing to have a varying data display on my chart?

TIA!

[Andy Pope]

You need to create a named range.

This example includes a static series and a dynamic one with a drop down to select year.

[F6Hawk]

Awesome, thanks so much, Andy!! I see now that I was on the right track with naming, but I was trying to name only a portion of the range, not the entire range. With your example, I was able to capture the data I needed. Much appreciated!