# Use OFFSET to show last 6 months' data over columns

#### slamanager

##### Board Regular
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.

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### SydneyGeek

##### MrExcel MVP
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.
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)

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
Code:
``=SERIES(Sheet2!\$A\$2,Sheet2!\$B\$1:\$J\$1,Sheet2!\$B\$2:\$J\$2,1)``
change that to
Code:
``=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

Replies
0
Views
379
Replies
3
Views
125
Replies
12
Views
522
Replies
0
Views
337
Replies
2
Views
151

1,191,592
Messages
5,987,530
Members
440,099
Latest member
wai2kit

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back