Forcing formula to disregard cell movements/deletions

I am running a macro which copies data from a Master Data workbook to a new file created from a template. The template has formulas which analyze the data, however I need to delete certain columns from the data (not hide). I have already set the formulas up so that they will be referencing the correct data after certain columns are deleted, but instead they give me the "#REF!" error, saying that the formulas references are messed up because columns were moved.

So I will give this example that would even be easy to do without opening the file attached:

Column A has 3 values: 1, 1, 1
Column B has 3 values: 1, 1, 1
Column C has 3 values: 2, 2, 2

Column D has this formula: =SUM($A$2:$A$4, $B$2:$B$4)
(This just sums column A and column B)

The solution to that formula is 6. When you delete column B, I would like for the solution to change to 9. This is essentially what I am asking. Is there a way to force cell references to ALWAYS refer to the cell, regardless of cell movement?

formula_reference_example.xlsx

If you delete column B you get a false reference. (ref!)

In that case you can copy column C to column B and then delete column C.

Refrase the macro, so you get the above in your macro.

Would that be the only way to keep cell references? That would surely add a ton of overhead to my macro and doesn't seem to elegant. I suppose it may work, although I will have to test it and make sure it isn't throwing errors or warnings that it is overwriting other data.

I have also read about INDIRECT() but tried it and it didn't work at all, it gave me the same error or #VALUE!

Try

=SUM(INDEX(A2:A4,0):INDEX(B2:C4,0,1))

gives 6 initially - gives 9 when B:B deleted

Cutter, why do I get the feeling you are cheating somehow?

I see it targeting two columns, both B and C. I raced to look up the INDEX() function and this response really confuses me. From what I understand, INDEX returns the value of [row, col] within the specified range, for example: INDEX(B2:C4, 1, 1) would return 1 since "1" is in the first row and column of the range B2:C4

What is this sorcery