Thread: Charting Offset Functions

Hi,

I have a series of offset functions in a column...

=OFFSET(Sentiment!$A$1,0,0,COUNTA(Sentiment!$A:$A),3)
=OFFSET(Sentiment!$E$1,0,0,COUNTA(Sentiment!$E:$E),3)
etc.

Each offset refers to three columns in my data set: the date, the last price, and the year over year value.

I want to chart the values that each offset function refers to. When I go to a chart and enter the offset function into the data range, it works. However, it changes the offset function to what it refers to (for example, =OFFSET(Sentiment!$A$1,0,0,COUNTA(Sentiment!$A:$A),3) becomes =Sentiment!$A$1:$C$104).

As a result, when the data changes, the chart fails to update.

How can I have the data range always have the offset function, or even the cell that I keep the offset function in?

Thanks

Use your Offset functions within defined names (Insert > Name>Defined Name) and refer to those names in your chart sources(e.g. x axis =Sheet1!Name1). Let us know if you have problems.

ChemistB