3

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?

Improve this question

1 Answer 1

Reset to default
8

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 F$4.

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:

=INDIRECT("F4")

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.

Improve this answer
3
  • That's how I thought it should work but it doesn't seem to. I changed the reference from "= F6" to "=F$6" in the cell that I want to remain statically linked to F6 no matter how many rows are inserted above it. Then I tried it out by inserting a row above (creating a new F row). Now the cell that referenced F6 before is "= F$7". Any thoughts?
    – Kenneth
    Apr 26, 2011 at 19:28
  • The cell containing the reference to F6 doesn't move... just the contents of F6
    – Kenneth
    Apr 26, 2011 at 19:30
  • @Kenneth - you're right, sorry, I had misinterpreted the question. I've edited my answer to give you the correct solution. You basically need to put the cell reference you want to remain static inside the INDIRECT function, then any row insertions or deletions will not affect the reference.
    – Gareth
    Apr 27, 2011 at 1:30

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .