Excel help to lock formulas from adjust on cell insert

I have a excel workbook where there is a hidden sheet that basically pulls data from other sheets in the same workbook just using the = formula.

The problem I am having is if I add a column or cells to sheet the formula is pulling data from it adjust the formula on the hidden sheet. I do not want this to happen.

If I insert a new column C and then add data to column C I still want the hidden data sheet to pull the info from that column. Right now it skips column C.

This is the basic formula I am using: ='OME6500 Commissioning '!C2

Is there a way to lock the formula so it does not adjust on cell insertions on the "OME6500 Commissioning" sheet.

Use the INDIRECT function. In each cell, instead of using

='OME6500 Commissioning '!C2

use

=INDIRECT("'OME6500 Commissioning '!C2")

The reference will never change no matter what you do to the originating cell(s). If you need to fill down a large amount of cells, use

=INDIRECT("'OME6500 Commissioning '!C"&ROW(A2))

then you can fill down the indirect formula and you won't have to manually edit every cell.

Best of luck!