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.
Best Answer
Use the INDIRECT function. In each cell, instead of using
use
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
then you can fill down the indirect formula and you won't have to manually edit every cell.
Best of luck!