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)