I have set up an excel table where occasionally the data I have in several columns gets shifted down several rows to make room for new data. I also have a cell that references the top most cell in one of these columns. I want this cell to continue to pull the information for the top most cell in this column. However, when I paste the new information in (which moves all the rows down accordingly), the reference moves too. For example say the top cell is F4 and I paste in 10 rows, the cell in question now references F14 instead of F4. Is there a way to force this cell to be a static reference to a particular cell only?
You can put a dollar sign ($) in front of anything you want to remain a static reference if you copy the formula to other cells.
In the reference for cell
However if you insert or delete any rows before
The INDIRECT function creates a cell reference from a string it is given, in this case returning a reference to F4. By doing it this way Excel won't adjust the formula if you insert or delete any rows; it will always point to F4 no matter what.