How to keep excel formula consistent with inserting new columns

Question

In my sheet, my top row is a bunch of dates and I have columns separating the weeks.

| 1.1 | 1.5 | 1.12 | 1.29 |JANUARY TOTAL| 2.3 | 2.15 | FEBRUARY TOTAL|
----------------------------------------------------------------------

Below each date is the number of hours worked on a project, and in each "MONTH TOTAL" column is a sum of the time worked on each project per month.

When I created this spreadsheet, I put in a filler of 2 blank spaces, but since I can't predict when I'll be working on each project, I have to rely on inserting new columns if I decide to work on something on a day that isn't preset into the sheet.

The problem is that my formulas don't extend all the way to the MONTH TOTAL columns when I insert new columns. For instance, when I create my spreadsheet, my formula for one of the totals in the MONTH TOTAL column would be "=SUM(B2:C2)" but when I insert two more dates I need the formula to be "=SUM(B2:E2)".

Is there a way to encapsulate inserted cells into a formula so that it sums between a bound of two columns and any new inserted cells?

I'd honestly arrange the data in a columnar fashion and use a pivot table. You only need to adjust the data source and the pivot table will update. Here's an example. — Jerry

tacuba tacuba · Accepted Answer · 2019-02-25T18:18:20

You can use the INDIRECT function coupled with ADDRESS, ROW, and COLUMN.

In your stated example, let's assume your "MONTH TOTAL" formula is in D2 and currently contains =SUM(B2:C2). You will want to update the formula to:

=SUM(B2:INDIRECT(ADDRESS(ROW(),COLUMN()-1)))

Whenever you insert a new column, it will still SUM by referencing the values in the preceding columns.

How to keep excel formula consistent with inserting new columns

1 Answers