Simple non-volatile relative cell references?

mrblister

Board Regular
Joined
Nov 20, 2016
Messages
174
Office Version
  1. 2019
Platform
  1. Windows
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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,748
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 
Solution

mrblister

Board Regular
Joined
Nov 20, 2016
Messages
174
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Nov 20, 2016
Messages
174
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
May 2, 2008
Messages
37,748
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Same as before. You want an indirect reference, so…
 

Forum statistics

Threads
1,147,634
Messages
5,742,240
Members
423,717
Latest member
rubthenut

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top