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
  • 113
  • 4
  • 1
    sounds like VLOOKUP: https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1 – Scott Craner Oct 14 '21 at 15:01
  • thanks - I think my example maybe a little confusing since I am not wanting to lookup the BANANA text in a table - rather I would like to find the cell reference E4 and calculate an OFFSET based on that cell address. I used the BANANA to illustrate the example and make it easy but perhaps its confusing. – robbie70 Oct 14 '21 at 17:12
  • 1
    `=VLOOKUP(E9,E4:F5,2,FALSE)` – Scott Craner Oct 14 '21 at 17:17
  • Another formula `=INDEX(F4:F5,MATCH(E9,E4:E5,0))`. – Reddy Lutonadio Oct 14 '21 at 17:24
  • @ScottCraner thanks - I've tried and it would work but its doing a textual lookup. In my table I have two BANANAS - so its picking up the wrong OFFSET value. If it were to reference the specific cell address then it wouldnt. – robbie70 Oct 14 '21 at 17:29
  • 1
    Are you looking for something like: `=FORMULATEXT(E9)` you'll need to wrap with `SUBSTITUTE()` or some other text manipulation formula to get rid of the `=` – gns100 Oct 14 '21 at 17:32
  • @ReddyLutonadio thanks. I've tried your formula but cant get it to work - sorry. – robbie70 Oct 14 '21 at 17:34
  • @gns100 thanks. I am having two problems with this approach. I cant seem to include the =SUBSTITUTE(FORMULATEXT(H20), "=", "") within the OFFSET statement i.e. =OFFSET(SUBSTITUTE(FORMULATEXT(H20), "=", ""),0,1,1,1), gives me a syntax error. But secondly even if I split it into two separate steps, its applying the OFFSET to the cell referenced and not the contents which have been calculated, i.e. =OFFSET(G37,0,1,1,1) applies the offset to G37 and not to F11 which is what has been calculated using the formula you've suggested. – robbie70 Oct 14 '21 at 17:52

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
  • 455,459
  • 31
  • 526
  • 924
  • Thats worked thank you. I've combined your answer with that of @gns100 since it appears that you do need to remove the = sign from the cell reference. My final formula is like so, =OFFSET(INDIRECT(SUBSTITUTE(FORMULATEXT(H20), "=", ""),TRUE),0,3,1,1) – robbie70 Oct 14 '21 at 19:02
0

=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
  • 11
  • 1