Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Take active cell value in for loop and offset based on another cellvalue for columns to offset | Excel Programming | |||
Charts, HTML, offset | Charts and Charting in Excel | |||
OFFSET Charts - moving data length | Charts and Charting in Excel | |||
Find, Copy offset to offset on other sheet, Run-time 1004. | Excel Programming | |||
Dynamic Charts Offset | Charts and Charting in Excel |