Menu
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.
566
If you want to maintain the original cell reference when you copy it, you "lock" it by putting a dollar sign ($) before the cell and column references. For example, when you copy the formula =$A$2+$B$2 from C2 to D2, the formula stays exactly the same. This is an absolute reference.
Keep formula cell reference constant with the F4 key 1. Select the cell with the formula you want to make it constant. 2. In the Formula Bar, put the cursor in the cell which you want to make it constant, then press the F4 key.
To make a cell reference absolute: Within the formula, before the part of the reference (i.e., row or column reference) that you would like to be absolute, type $. In the Formula bar, Click within the cell reference you want to change. Windows: Press [F4].
Another reader recommended using the F4 function key to toggle between making a cell reference relative and absolute. Either double-click on the cell or press F2 to edit the cell; then hit F4. It works even when you highlight multiple cells.
Try =indirect("F2"). This will work if you know that the top-right cell of the table is always going to be $F$2.
167
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
