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. 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.
Let's define what a locked cell reference does: Locking cell references will. What is the Mac Excel formula to have paragraphs when merging text in one cell? The data entry cell will need to be different to the formula calculation cell otherwise you will get a circular reference, however you can set the reference to only reference a certain cell, certain column or certain row. By adding a $ to various parts of the reference.
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.