cell format A1 style equivalent to RC[-1]

Is there a way in cell format A1 style equivalent to RC[-1]?
Saying it another way, how do you refer to the cell to the left of the current cell in ‘A1’ style?


Absolute and relative addressing in help

Of course, OFFSET allows to get the relative reference, but I think it’s important to realize that simple ‘A1’ is in fact internally a relative reference, which is only shown as some specific address, but internally is kept by the program as a “N columns lo the left/right, M rows above/below” relative to the cell it’s in.

So when you are in B1, and have =A1 in A1 notation, it is exactly the same as RC[-1] in R1C1 notation, and means exactly “reference to cell on the same row, one column to the left”. That is apparent when you copy the cell and paste to another place, where the formula will be shown to refer the cell to the left of the new location.

@mikekaganski - sorry for making your comment looking a bit unmotivated due to the fact, that I have deleted my comment. I realized my comment about using OFFSET(B1;0;-1) is pointless with respect to implementation of addressing schemes (as you explained in excellent clarity). Sorry again …

@anon73440385: please no sorries! No worries :smiley: I thank you for mentioning the OFFSET here - in fact, it is useful to highlight that one may think about more complex solutions (which look explicit and natural), but the simpler solution is better (although needs to familiarize oneself with the concept). Thanks again!

I disagree, =A1 is only similar to RC[-1] if you are in B1 If you are in L13, =A1 does not point to K13 where as RC[-1] does.

In looking at OFFSET, it’s use seems to require knowing which cell your in and again doesn’t work the same unless there is some way to say OFFSET(thisCell;0;-1) and I don’t know how to do that short of ThisComponent.CurrentSelection.cellAddress from which I could get the column and row but that would have be converted to A1 nomenclature which Pitonyak showed how to do but isn’t trivial.

So what am I missing? I’m not used to using the A1 style of addressing and don’t see a solution to the problem, perhaps it is because I’m looking for how to do this in a macro rather than with the GUI.


If you need to have formulas look the same regardless of the cell you are in, then just use R1C1 (this property was the distinction compared to A1 in the first place). Options|Calc|Formula|Formula syntax.


… you just should have mentioned that you are in fact talking about BASIC and not about the GUI.

You are right, Opaque.

For my purposes the GUI is only for looking at the results of the Basic manipulations of the data and I don’t think to make that point when I ask a question.

From the discussion here I take it that there is no way in Basic to refer to the cell to the left of the current cell using the A1 cell reference style without knowing the A1 address of the current cell.

I guess that is why I couldn’t find that.

Thanks and be well,

As said: just use R1C1 syntax, also in your Basic. It is available in VBAcompatibility mode: VBA XRange attributes