Excel: how can I find a cell being referenced?
Suppose I'm at cell C1, and the code in it is "=A1". Then in cell D1, I want to see that C1 is linking to A1, and then link to B1 instead.
More generally, if a cell X is linking to a cell (a,b), is it possible to get the value in the cell (a+1, b+1), based only on X? If not, is there a simple way to so something similar?
EDIT: As a concrete example, suppose we have the following arrangement of cells:
I want to extend the "=A1" command downwards through column E in order to copy the cells "Apples", "Bananas", "Pears", etc. Then I want to create a command in F1 which I can similarly extend downwards throughout F, which will copy the contents in column B, i.e. "1$", "3$", "2$", etc. Crucially, the command in F1 should work even if I reference a different cell from E1. That is, if I decide to make E1 reference B1 instead, then F1 should contain the contents of C1, without having to change the formula in F1.
23 Answers
You can switch Excel between displaying the formula and displaying the results. In Windows, it's Ctrl-grave . That's the key next on the top left, between number 1 and Tab.
Is this what you are after
3=FORMULATEXT(C1)
I will work from the example given. First, use the FORMULATEXT command to get the formula in E1 as text.
=FORMULATEXT(E1)This gives the string "=A1"
Then, we have to trim the text to just get the cell. I used the RIGHT command, though there may be a less verbose way to do it.
=RIGHT( FORMULATEXT(E1), LEN( FORMULATEXT(E1) ) - 1 )This gives the string "A1"
Then, we have to turn this string into a reference, using the INDIRECT command.
=INDIRECT( RIGHT( FORMULATEXT(E1), LEN( FORMULATEXT(E1) ) - 1 ) )This references the cell A1
Finally, we can get the cell next to A1 using the OFFSET command.
=OFFSET( INDIRECT( RIGHT( FORMULATEXT(E1), LEN( FORMULATEXT(E1) ) - 1 ) ), 0, 1)0This references the cell B1, which is what we wanted.
