Ask Your Question

Revision history [back]

That is not a new behaviour, it has always been like that, and it is expected and implemented by most (if not all) major spreadsheet applications. If you drag&drop or cut&paste cells that are individually referenced by other cells, those references are adjusted to point to the new location.

If you want location "persistent" references then use a named range/expression with a relative reference, e.g. for Mike's screencast example on cell Sheet1.A1 define a named range thatcell to $Sheet1.A1 (note it's not $Sheet1.$A$1) and in Sheet2.A1 use the formula =thatcell. As both column and row reference particles are relative you can use the same =thatcell formula expression in Sheet.A2 to obtain the the value of Sheet1.A2 and so on. The underlying relative reference of such named expression is not adjusted if cells are moved.