I'm looking for a simple, non-volatile way to relatively reference a cell in formulas (e.g. the contents of the cell above, or to the left, etc). For example, if I have a formula in B2, and I want it to reference the contents of the cell to the left, then I would use "A2" in the formula. But this creates a problem if I move A2; I want the formula in B2 to always reference the contents of the cell to it's left.
So instead of "A2", I could use "OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)" (or similar), but Indirect and Offset are volatile.
I also don't want to switch to R1C1 notation, for this one edge-case.
I'm aware of Named-Range and UDF solutions, but am wondering if better solutions exist.
So does a SIMPLE, NON-VOLATILE solution(s) exist?
So instead of "A2", I could use "OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)" (or similar), but Indirect and Offset are volatile.
I also don't want to switch to R1C1 notation, for this one edge-case.
I'm aware of Named-Range and UDF solutions, but am wondering if better solutions exist.
So does a SIMPLE, NON-VOLATILE solution(s) exist?