SUM Formula when book end cells are changed

[Yandeez]

Hi,

A monthly report that I distribute uses the SUM Formula. When I update the report with next month's totals, how can I have that sum automatically update and include the new month? Workbook attached for reference.

Thanks in advance.

 

[NARAYANK991]

Hi ,

You need to either provide columns for all the months , and have the Totals column after 12 columns , or have the Totals column before the column for the first month.

Narayan

 

[NARAYANK991]

Hi ,

Otherwise , ensure that in the next month , instead of doing a Cut & Paste , you do a Copy & Paste.

See the attached file.

Narayan

 

[Somendra Misra]

Hi,

Another alternative Use below formula, where in if you insert columns it will adjust automatically.

=SUM(OFFSET($A2,,,,COUNT(INDIRECT(ADDRESS(ROWS(I$1:I2),1)&":"&ADDRESS(ROWS(I$1:I2),COLUMNS($A2:$I2)-1)))))

Regards,

 

[NARAYANK991]

Hi ,

Another option if you insert columns between column A and the Totals column :

=SUM(OFFSET(INDIRECT(ADDRESS(2,COLUMN())),,-COLUMN()+1,1,COLUMN()-1))

Narayan

 

[sschwant]

another option if your worksheets are consistent cell by cell you could link all worksheets between two as you say "bookends" say ws name Start and a ws named End ... then you can sum all the cells on each sheet within that range w/ in your wb using the formula:

=SUM('Start:End'!H195)

then all you have to do is drop in your current month between the Start and End tabs in your book and then have a summary sheet which tallies up automatically ...

key caveat ... data and cells have to be in the same location on each sheet

 

[bobhc]

Why not just use a table, auto month placement and auto total update

 

[Yandeez]

Decided to just include all the months in the SUM formula and just hide then unhide the columns (months) as we progress through the year. Thanks everyone for the help.