Search code examples
google-sheetsreferenceformula

Google Sheets Cell Reference Shifts When Deleting Column

I have a simple sheet where each column references the column before it. It is meant for a progressive budget to predict interest patterns.

Essentially, it follows this pattern:

  • Cell C2 has a formula that references Cell B2.
  • Cell B2 is a static number.
  • Cell D2 references the number in Cell C2.
  • Cell E2 references the number in Cell D2.
  • So on and so forth.

Each column represents a week, so as time progresses, I delete the column for the previous week to consolidate down the data. Each time I delete a column, such as Column C, Column D becomes C, but the references to C2 do not update to B2. I get the standard #REF error. Is there a formulaic method to avoid this so I don't have to manually update each reference every time I delete a column?

I have tried using INDIRECT or $ tags to force the formula to update based on the column deletion, but I can't figure out how to make it work.

As a direct example:

  • Cell C2: "=B2+SUM(C3:C6)"
  • Cell D2: "=C2+SUM(D3:D6)"
  • Delete Column C. Column D becomes C.
  • Currently New Cell C2: "=#REF!+SUM(C3:C6)"
  • Desired New Cell C2: "=B2+SUM(C3:C6)"
Solution

  • In C2 instead of:

    =B2+SUM(C3:C6)

    Use the following formula and drag it to the right:

    =INDIRECT("C[-1]",0)+SUM(C3:C6)

    C[-1] in 'R1C1 notation' means one cell to the left of the current cell so when you delete the column to the left it doesn't result in a #REF! error but it retrieves the new cell to the left.