Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 3 down vote favorite

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?

share|improve this question

1 Answer

up vote 5 down vote accepted

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.

share|improve this answer
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 '11 at 19:28
The cell containing the reference to F6 doesn't move... just the contents of F6 – Kenneth Apr 26 '11 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. – 8088 Apr 27 '11 at 1:30
Thanks! That was exactly what I needed! – Kenneth Apr 27 '11 at 4:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.