1

I would like to know if its possible to extract the cell reference from a cell to use to calculate an offset.

I've added the below screenshot to illustrate what I am trying to achieve I hope that it makes sense.

I've tried using the =OFFSET formula as in,

=OFFSET(E9,0,1,1,1)

but this says I've created a circular reference. I would like to tell it to extract the cell reference contained in cell E9 i.e E4 to then calculate the qty value in the adjacent cell so in this case 2. But if I change E9 to be E5 then the =OFFSET formula should return 3.

enter image description here

robbie70
  • 125

2 Answers2

1

To convert a text string into a valid cell reference in Excel, you can use the INDIRECT function.

From the above link:

For example, take a look at the INDIRECT function below.

enter image description here

Explanation: =INDIRECT(A1) reduces to =INDIRECT("D1"). The INDIRECT function converts the text string "D1" into a valid cell reference. In other words, =INDIRECT("D1") reduces to =D1

harrymc
  • 498,455
1

=offset(indirect(right(formulatext(E9),len(formulatext(E9))-1)),0,1)

Use the offset function to refer to the relative location. To get the pre-offset location, you need to use the indirect function. To get the text of the pre-offset location to provide to indirect, you can retrieve it with the formulatext function. However, it will have the = sign as part of the formula. So you use the right function to return the entire text of the formula, minus the left-most character. To determine how many characters you need to use, we use the len function and subtract one.

Dana Black
  • 36