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.

Sambo

Posted 2018-09-11T22:36:51.627

Reputation: 101

1Not sure how to trace back to the root of assignment, but you can use OFFSET to find (a+1,b+1) if you know (a,b). – Zhongjie Shen – 2018-09-11T23:02:35.883

1@Sambo, did you try entering "=FORMULATEXT(C1)" in cell D1. Is this what you are looking for? – Bharat Anand – 2018-09-12T03:26:35.050

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. https://support.office.com/en-us/article/display-or-hide-formulas-f7f5ab4e-bf24-4efc-8fc9-0c1b77a5356f

Christopher Hostage

Posted 2018-09-11T22:36:51.627

Reputation: 4 751

Is this what you are after

=FORMULATEXT(C1)

Bharat Anand

Posted 2018-09-11T22:36:51.627

Reputation: 346

A downvote on a seemingly perfect answer without any explanation! I am happy to delete the answer if this is not what the OP is looking for. If I was unsure, I would have added my answer as a suggestion to the comments, however I am pretty sure this will solve @Sambo's problem. – Bharat Anand – 2018-09-12T03:25:13.480

1I suspect the downvote is because this hint is barely a comment, let alone an answer. It doesn't explain anything. If the OP was familiar with FORMULATEXT and that's what they need, they wouldn't have asked the question. So assuming they simply aren't familiar with it, why should they use this? What does it do? How does it solve the problem? Where can they learn more about the function? – fixer1234 – 2018-09-12T06:17:59.537

This command does indeed help! Though I will post a more complete answer that I figured out – Sambo – 2018-09-12T14:58:30.633

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)

This references the cell B1, which is what we wanted.

Sambo

Posted 2018-09-11T22:36:51.627

Reputation: 101