Excel - Dynamic Charts

Question

I have a series that looks like SERIES('Data'!$C$140;'Data'!$E$138:$Q$138;'Data'!$E$140:$Q$140;2) and in order to make it dynamic I'd like to replace the endpoints with a cell that depends on the start point, like this: SERIES('Data'!$C$140;'Data'!$E$138:OFFSET($E$140;0;12);'Data'!$E$140:OFFSET($E$140;0;12);2)

But this is not working. How do I go about doing this?

Answer 1

EDIT:

It turns out that OFFSET() is not allowed in a SERIES() function. Neither is INDEX(). Even with the corrections below, your technique won't work to make the chart dynamic.

However, there are two other ways to make dynamic charts:

1. Using a Table as the data source when creating the chart.
2. Using a dynamic Named Formula for each of the series.

See this site for a tutorial on applying these two methods.

---

ORIGINAL ANSWER:

You have two issues that I can see.

For starters, you have a typo. The first argument of the first OFFSET() should be $E$138 not $E$140.

Secondly, you're missing the sheet references for the first arguments of both OFFSET() functions.

If the operand after the : operator is a hard-coded value, like the $Q$138 in 'Data'!$E$138:$Q$138, a sheet reference for it is not required. If the operand is a function, then the same sheet reference as used for the operand before the : must be used for the reference returned from the function.

The corrected formula is:

SERIES('Data'!$C$140;'Data'!$E$138:OFFSET('Data'!$E$138;0;12);'Data'!$E$140:OFFSET('Data'!$E$140;0;12);2)