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
F4, you can change it to
However if you insert or delete any rows before
F4, Excel will automatically adjust the formula (e.g. inserting a row,
F$4 would become
F$5). To prevent this you can wrap the cell reference with the INDIRECT function as follows:
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.