INDIRECT() works great for preserving the absolute position of references after the targets have been copied and pasted, but the problem is that it also preserves the absolute position when the formula is copied, meaning that you can't easily extend a formula to cover a large range when using it.
The solution is to combine
ADDRESS() to programmatically generate the position of the target cell based on the formula's cell.
In the simplest case, such as when the target cell has a fixed column and always stays in the same row as the formula, this can be done as follows:
In order to introduce dynamic offsets from the formula cell, you can use
In the below screenshot, the formulae in the
B1:E1 were extended to the 16 rows beneath, and then the number sequence in
A7:A10 was cut and pasted 6 cells down. As you can see, the simplest formulae were automatically adjusted and desynchronized, while the naive use of
INDIRECT() did not extrapolate across all rows properly, but the two formulae that use
INDIRECT() along with programmatic retrieval of row and column locations were able to maintain their references: