Use OFFSET to show last 6 months' data over columns

[slamanager]

Hi,

I've read some posts on this subject and I would realy like some clear instruction on using OFFSET when data is arranged left to right in columns. And each column is headed by a month title. then getting charts to only show data the last six months from say column c to when ever the data stops.

I am really getting sick of trying to make the offset function work horizontially not vertically.

I have like 50 charts to refrence and this will be the biggest thing in the world if i could get a really quick fix.

 

[SydneyGeek]

You can do this by creating dynamic ranges and attaching them to your charts. Try this sample setup, then adapt to your problem:

Data on Sheet2.
A1 is City.
A2:A4 are Sydney, Adelaide, Melbourne
B1:J1 are month headings (Jul-06, Aug-06, etc)
B2:J4 is random data

Create these ranges (Insert | Name | Define)
Range: Dates
Reference: =OFFSET(Sheet2!$A$1,0,COUNTA(Sheet2!$1:$1)-1,1,-6)

Range: Sydney
Reference: =OFFSET(Sheet2!$A$2,0,COUNTA(Sheet2!$2:$2)-1,1,-6)

Range: Adelaide
Reference: =OFFSET(Sheet2!$A$3,0,COUNTA(Sheet2!$3:$3)-1,1,-6)

...you get the pattern. Check the syntx for the OFFSET function to see what the various parts do. Basically, this is finding the last value in a row and extending the range backwards from there so you get the last 6 values.

OK, chart rows 1 and 2.
Now select any one of the data points and look at the Series formula in the formula bar. It will say

=SERIES(Sheet2!$A$2,Sheet2!$B$1:$J$1,Sheet2!$B$2:$J$2,1)

change that to

=SERIES(Sheet2!$A$2,Book1!Dates,Book1!Sydney,1)

Press Enter to get out of the formula bar. Now, add more data and you will see the chart update. You will always get the last 6 months.

You'll need to do the same for all rows on the sheet. Takes some setup but once you are done, you won't have to touch them again.

Denis