Simple non-volatile relative cell references?

[mrblister]

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?

 

[RoryA]

Not really. You can't use a direct reference if you don't want moving that cell to affect the result, and indirect references, relative to the formula cell, are volatile. You can use something like =INDEX(A:A,row()-1) for example, if that would suit your particular purpose, though it's not fully relative to the formula cell.

 

[mrblister]

though it's not fully relative to the formula cell.

... took me a minute for me to figure out what you meant by "not fully relative", but your solution does work quite well. Thank you!

 

[mrblister]

Actually, I'm still trying to resolve an issue related to this formula. Continuing with my original example, if I want to reference the contents of the cell to the left of my formula cell (B2), I could use the formula: =INDEX(2:2,COLUMN()-1). This formula is stable (maintains it's relative reference to the "cell to it's left") if I insert/delete columns, or if I copy/paste the formula to a different location. However, if I move/drag the formula cell up or down, the relative reference breaks. The issue is in the "2:2" part of the formula, if I drag the formula cell down one cell, that part should be "3:3" but maintains "2:2" (but if I copy/paste the formula cell it's fine as that part becomes "3:3"). So I think the solution is that the "2:2" part of the formula should return a row array reference to whatever row the formula cell is in, something like "ROW():ROW()" ...but that doesn't work. Thoughts?

 

[RoryA]

Same as before. You want an indirect reference, so…