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.