Google-sheets – How to get a cell reference used in a cell for use in a different formula

formulasgoogle sheets

Suppose cells in a column (TheSheet!A) contain just references to cells in the column of another sheet, but noncontiguously. For example, give Sheet!A17 the formula:

=OtherSheet!A23

Other cells on TheSheet in the same row correspondingly need to reference cells in the referenced row. For example, TheSheet!B17 would need to reference OtherSheet!D23. While the references could be written directly, it would be error-prone. How can these other cells instead use the cell references from column A in their own formulae?

Note the value of the cell isn't a cell address, so INDIRECT is insufficient. Similarly, ROW by itself is unsuitable as it will return the row of the cell itself, not the cell it references.

Best Answer

The key is FORMULATEXT, which will return the formula used in a cell.

=FORMULATEXT(A17)

This value will then need to be parsed into a cell address. As the formula is simply a reference, removing the leading = is sufficient. RIGHT, in its ltr-centric way, can be used to get the trailing portion of a string, though this approach is a little verbose, as it requires the length of the string, requiring FORMULATEXT to be used twice. Consequently, we're not going to use it, though it's presented here for reference:

=RIGHT(FORMULATEXT(A17), 2, LEN(FORMULATEXT(A17))-1)

SUBSTITUTE is an option (SUBSTITUTE(FORMULATEXT(A17), '=', '')). A substring function that accepted negative indices would be just about perfect, but Google Sheets doesn't offer one. What it does offer is MID, which can be made to work. It also takes a string length; a value longer than the (right portion of the) string can be given and the extra length is essentially ignored.

=`MID(FORMULATEXT(A17), 2, 100)`

While this is not technically correct, it shouldn't cause an error in this use case, since the formula should never be close to 100 characters long. Another option is REPLACE to replace the "=" substring with an empty string: REPLACE(FORMULATEXT(A17), 1, 1, ""). We'll use MID for brevity & clarity (such as there is in spreadsheet formulae).

We now have a cell address as text. There are a couple ways this can be turned into a cell reference on the same row but in another column. Presented here is OFFSET, which shifts cell (technically range) references by a given number of rows & columns. For example, to go from column A to column C, use OFFSET(range, 0, 3). Since OFFSET takes references, rather than text addresses, the value from the previous formula is passed first through INDIRECT.

=OFFSET(INDIRECT(MID(FORMULATEXT(A17), 2, 100)), 0, 3)

This is the desired cell reference.

Related Topic