Reference the location of a cell that is referenced by another cell

Question

I am posting this question because I had a hell of a time trying to find the answer myself.

Basically I have a cell that references a cell that references another cell with some data in it. For example, A3=A2 and A2=A1 and cell A1 contains the text Hello. So cell A2 and A3 also contain the same text. See picture below:

But let's say I actually want cell A3 to show data relative to the cell position that A2 is pointing to (Remember A3=A2). I need to use the OFFSET function to do this and one would think that A3=OFFSET(A2, 0, 1) might work (click here to see how OFFSET works). But OFFSET does not work by itself. It would return the data from the cell to the right of cell A2 (shown below), instead of realizing that A2 points to A1 and then returning the data to the right of A1.

So how then do we get cell A3=B1, indirectly, by going through cell A2?

Answer 1

We need to use a combination of functions, one of which is INDIRECT. click here to see how INDIRECT works. So in order to use INDIRECT we need the text found in cell A2. The FORMULATEXT function (more info) will extract =A1 from cell A2 when used like so in cell A3:

A3=FORMULATEXT(A2), shown below:

Now we just need to strip off the = from the text so that we have an actual text reference that INDIRECT can use. You can do this using either the RIGHT or MID functions (right, mid) in combination with the LEN function (length). You need LEN if the number of rows in your sheet goes from single digit numbers into double digits, and so on. You also need to pass in the FORMULATEXT function again so it can compute the length of the text =A1. If you don't, it will compute the length of Hello. You also need to be aware that LEN()-1 is the correct length you need, since you are throwing away the = from the text.

For example: A3=RIGHT(FORMULATEXT(A2),LEN(FORMULATEXT(A2))-1) giving us:

Put it all together and you can OFFSET the cell that A2 references from A3 with INDIRECT like so:

A3=OFFSET(INDIRECT(RIGHT(FORMULATEXT(A2),LEN(FORMULATEXT(A2))-1)),0,1)