How do I pull data for consecutive months in an Excel spreadsheet

[Patti Ayala]

Every month I have to add the next consecutive month's data to my formulas in
an Excel spreadsheet. For instance, in March 2008 I need to include YTD
sales comparison data from a work sheet that has 2007 monthly sales, so my
formulas pull from the January, February and March 2007 cells. When I move
on to April 2008, I have to go back and manually change my formulas to add
the cells that include April 2007 sales data. Is there any way to set this
up so I don't have to make these changes manually every month?

 

[Glenn]

Every month I have to add the next consecutive month's data to my formulas in
an Excel spreadsheet. For instance, in March 2008 I need to include YTD
sales comparison data from a work sheet that has 2007 monthly sales, so my
formulas pull from the January, February and March 2007 cells. When I move
on to April 2008, I have to go back and manually change my formulas to add
the cells that include April 2007 sales data. Is there any way to set this
up so I don't have to make these changes manually every month?

I'm sure there is, but it would help if you would show what your data and
formula looks like now.

 

[Spiky]

Assuming your data starts in B2 and goes sideways, this can be in
whichever column you want, D2, H2, etc:

=SUM(B2:OFFSET(B2,,,,COLUMN()-COLUMN(B2)))

And it should work as you add columns, even if they are right next to
this SUM.