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