Google Sheets, Referring to a cell range

Question

Here's what I am trying to do.

In cell C15, I put the cell reference of another cell, e.g. B2.

I another cell, cell C16, I want to check that IF the value in cell C15 contains the cell reference to any cell between B5:D10.

How could I do that please?

Many thanks!

Answer 1

How to tell whether a given formula references a cell in a given range.

• Cell C15 references a single cell (such as,=B2).
• Is the cell referenced in cell C15 in a given range (such as, B5:D10)?

What is the referenced cell?

=FORMULATEXT(C15)

• this returns the formula as a string.
• such as, "=B2"

What is the row of the referenced cell

=value(REGEXEXTRACT(FORMULATEXT(C15), "[0-9]+"))

• REGEXEXTRACT' returns the Row number of the referenced cell as a string
• VALUE converts the string to a number.

What is the column of the the referenced cell?

=column(indirect(REGEXEXTRACT(FORMULATEXT(C15), "[a-zA-Z]+")&"1"))

• REGEXEXTRACT returns the column letter of the referenced cell
• Column(Indirect(<<cell letter>>&"1")) - converts the column letter to the column index

Is the referenced cell in the given range: B5:D10

Start by referencing the index values of the range.

• startColumn = 2, endColumn = 4, startRow = 5, endRow = 10

  Is the row in the range?

  If the row number is greater than or each to the "startRow" AND less than or equal to the "endRow" then the row in in the range.

  =if(and(value(REGEXEXTRACT(FORMULATEXT(C15), "[0-9]+"))>=5,value(REGEXEXTRACT(FORMULATEXT(C15), "[0-9]+"))<=10),TRUE,)

  Is the column in the range?

  If the column number is greater than or each to the "startColumn" AND less than or equal to the "endColumn" then the column in in the range.

  =if(and(column(indirect(REGEXEXTRACT(FORMULATEXT(C15), "[a-zA-Z]+")&"1"))>=2,column(indirect(REGEXEXTRACT(FORMULATEXT(C15), "[a-zA-Z]+")&"1"))<=4),TRUE,)

  End Result:

  If both formula are TRUE, then then the referenced cell is in the given range. Otherwise it is not in the given range.

The complete formula

=if(and(if(and(value(REGEXEXTRACT(FORMULATEXT(C15), "[0-9]+"))>=5,value(REGEXEXTRACT(FORMULATEXT(C15), "[0-9]+"))<=10),TRUE,)=TRUE,if(and(column(indirect(REGEXEXTRACT(FORMULATEXT(C15), "[a-zA-Z]+")&"1"))>=2,column(indirect(REGEXEXTRACT(FORMULATEXT(C15), "[a-zA-Z]+")&"1"))<=4),TRUE,)=TRUE),TRUE,FALSE)