# Simple non-volatile relative cell references?

#### mrblister

##### Board Regular
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?

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### RoryA

##### MrExcel MVP, Moderator
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

##### Board Regular
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

##### Board Regular
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

##### MrExcel MVP, Moderator
Same as before. You want an indirect reference, so…

Replies
3
Views
333
Replies
2
Views
422
Replies
4
Views
365
Replies
0
Views
398
Replies
6
Views
369

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

1,151,604
Messages
5,765,383
Members
425,281
Latest member
tmoreira001

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back