Dynamic chart range using INDIRECT That function is not valid despite range highlighted

Question

I'm attempting to use the INDIRECT function to build a dynamic range for a chart. Excel indicates the corresponding range on the sheet, indicating that it recognizes the range I'm creating with INDIRECT:

I get an error message saying the function is not valid when saving the chart,

Does anyone know the solution to the issue or how to make a dynamic chart range with defined start and end points?

Answer 1

Similar to Sean's fantastic response, mine also provides for a start and finish day. To choose the start and end days depending on E2 and E3, first, build two named ranges using Index/Match formulae as follows:

rngDay

=INDEX(Sheet1!$A:$A,MATCH(Sheet1!$E$2,Sheet1!$A:$A,0)):INDEX(Sheet1!$A:$A,MATCH(Sheet1!$E$3,Sheet1!$A:$A,0))

rngValue

=INDEX(Sheet1!$B:$B,MATCH(Sheet1!$E$2,Sheet1!$A:$A,0)):INDEX(Sheet1!$B:$B,MATCH(Sheet1!$E$3,Sheet1!$A:$A,0))

You can then click the series in the chart and modify the formula to:

=SERIES(Sheet1!$B$1,Sheet1!rngDay,Sheet1!rngValue,1)