14

I have a spreadsheet where I want cell formula to always look at a specific cell, even if rows or columns are inserted and the specific cell moves. Effectively, I always want to look at the 'top' cell of a table, even if new rows are inserted at the top of the table.

eg. Cell A2 has the formula[=$E$2]

Now I highlight row 1 and do Insert Row. The formula in A2 now says [=$E$3] but I want it to be looking at the new row 2.

The dollars will keep an absolute cell reference no matter what I do to the 'referencing' cell, but I want the cell reference to be absolute no matter what I do to the 'referenced' cell. If that makes sense!

Effectively, I have a 'table' in excel 2007 and I want to always reference the top row. The trouble is that rows are added to this table from the top so the top row keeps moving down to make room for a new top row.

--- Alistair.

Mark Meuer
  • 7,200
  • 6
  • 43
  • 64
user41013
  • 1,251
  • 2
  • 16
  • 25

3 Answers3

14

Try =indirect("F2"). This will work if you know that the top-right cell of the table is always going to be $F$2.

ktdrv
  • 3,602
  • 3
  • 30
  • 45
  • 1
    Generally speaking, it is advisable to avoid INDIRECT, and use other means such as `=INDEX($E:$E, 2)` – MacroMarc Feb 02 '17 at 19:29
  • Fair point, though I did make sure to point out that you have to be pretty sure the top-right cell of the table is going to remain fixed. Also, using `INDEX` only helps if new columns are being inserted but the `2` in the 2nd argument there still gets you if rows are added. – ktdrv Jul 06 '17 at 07:07
6

You could also use the Offset Function:

http://office.microsoft.com/en-us/excel-help/offset-function-HP010342739.aspx

or

https://support.office.com/en-us/article/offset-function-c8de19ae-dd79-4b9b-a14e-b4d906d11b66

MattAllegro
  • 6,455
  • 5
  • 45
  • 52
hyjinx
  • 61
  • 1
  • 1
  • Please, can you add some more information from docs about this function for others? Quote an important part or an example of usage. Links could become broken. – AndrewShmig Apr 04 '18 at 20:44
  • I had a need to do this yesterday. The OFFSET function really is quite simple, it works like this: OFFSET(reference_cell;offset_rows;offset_columns). For example: OFFSET(F4;1;0) means get the value which is one row down from cell F4. Use negative offset values for "rows up" or "columns left". I found this to be a good way to guard cell references against rows or columns being inserted. – krschn Nov 11 '20 at 09:52
1

Building on @ktdrv's answer (I can't comment): =indirect("F"&ROW()) would be $F2 if it's a reference that needs to be dragged down multiple rows. A minor drawback with =indirect() is that you lose cell reference highlighting for the formula.

sbha
  • 9,802
  • 2
  • 74
  • 62