Using =Offset in Charts
 

I'm trying to update a chart so that it will handle different ranges fluidly. I've read quite a few different articles on setting up a data series that references a named range, which in turn is set up with an =offset. Unfortunately, I just can't make this work.

Say that I have three years of monthly data stored in cells A1:AJ1 (36 columns) on Sheet1 and a chart that presents a simple bar chart with 12 months of data. I want to be able to present any 12 month period from within these 36 months by updating a cell that indicates the first month to present.

Then, let's say that in cell C1, I have an integer stored that keeps track of this first month. If I wanted to present the data in D1:O1 (12 columns), I would want to offset by 4 columns from column A, right? So, I make a range called "period" in which I type the following: =offset(Sheet1!A1,0,Sheet1!C1,0,12).

In my chart, I get to Edit Series, and in the Series Value field, I input "=period", which I think should allow me to change cell C1 and have the chart report on any 12 month period that I want. (note: I also tried entering =Sheet1!period)

But, I get an error as soon as I hit the enter key: "A formula in this worksheet contains one or more invalid references." I think that the problem relates to how I set up the named range, but I don't know what I am doing wrong. Can anybody suggest how I can fix this?

Thanks!

Using =Offset in Charts
 

On Monday, July 16, 2012 6:01:25 PM UTC-6, (unknown) wrote:
I'm trying to update a chart so that it will handle different ranges fluidly. I've read quite a few different articles on setting up a data series that references a named range, which in turn is set up with an =offset. Unfortunately, I just can't make this work.

Say that I have three years of monthly data stored in cells A1:AJ1 (36 columns) on Sheet1 and a chart that presents a simple bar chart with 12 months of data. I want to be able to present any 12 month period from within these 36 months by updating a cell that indicates the first month to present.

Then, let's say that in cell C1, I have an integer stored that keeps track of this first month. If I wanted to present the data in D1:O1 (12 columns), I would want to offset by 4 columns from column A, right? So, I make a range called "period" in which I type the following: =offset(Sheet1!A1,0,Sheet1!C1,0,12).

In my chart, I get to Edit Series, and in the Series Value field, I input "=period", which I think should allow me to change cell C1 and have the chart report on any 12 month period that I want. (note: I also tried entering =Sheet1!period)

But, I get an error as soon as I hit the enter key: "A formula in this worksheet contains one or more invalid references." I think that the problem relates to how I set up the named range, but I don't know what I am doing wrong. Can anybody suggest how I can fix this?

Thanks!

I got it. A couple of errors here, but the significant on is that the =offset(Sheet1!A1,0,Sheet1!C1,0,12) is not zero based, so I needed to put a 1 there.

Thanks anyway.