Locking a formula through Add-Column

[IceCreamMan]

I have a formula on tab5 that references $B$3 on the other four tabs.

This is for inventory, so every week I add a new column between the Item Name and Quantity. I just need the formula to not adjust, so that it always reads column B, and not what moved from column B.

“Total” Code:
=SUM(‘Freezer1’.$B$7,‘Freezer2’.$B$7,‘Freezer3’.$B$7,‘Freezer4’.$B$7)

Before Column Add:

- A  -                  - B   -                     -  C-

ITEM: ---------------------------- 04.17.16

Strawberry --------------------------- 5

TOTAL: 5 (Correct)

After Column Add:

- A  -                  - B   -                     -  C-

ITEM ------------------------------04.21.16 -------------------------------04.17.16


Strawberry-----------------------------3----------------------------------------5

TOTAL: 5 (Incorrect) (Should be 3)

[LeroyG]

Try with =SUM(VLOOKUP(A5,$Freezer1.A3:$C3,2,0),VLOOKUP(A5,$Freezer2.A3:$C3,2,0),VLOOKUP(A5,$Freezer3.A3:$C3,2,0),VLOOKUP(A5,$Freezer4.A3:$C3,2,0)) in column B.

There should be a more elegant formula, but… I am still learning.

Do not move column B to column C. Just select the four spreadsheets tabs, secondary click on the column B header, choose Insert Columns Before.

Or select the four spreadsheets tabs, place the cell cursor on column B, choose menu Sheet - Insert Columns - Columns Before.

See sample file.

LibreOffice 6.3.6.2 (x86); OS: Windows 6.1

---

Add Answer is reserved for solutions.

Press edit below your question if you want to add more information; also can comment an answer.

Check the mark () to the left of the answer that solves your question.