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

Question

I'm trying to create a chart with a range built dynamically using the `INDIRECT` function. Excel does recognize the range I am creating using `INDIRECT` as it highlights the corresponding range on the sheet: However when saving the chart, I get an error message saying the function is not valid: Does anybody know what the problem is / how to create a dynamic chart range from a specific start to specific end point? PS: You can download the above spreadsheet here. The formula I was using: `=INDIRECT("sheet!"&E2&":"&E3)`

Accepted Answer

Mine is similar to Sean's excellent answer, but allows a start and end day. First create two named ranges that use Index/Match formulas to pick the begin and end days based on E2 and E3:

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)
```

Here's a nice Chandoo post on how to use dynamic ranges in charts.

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

Solution

Problem