CODEWITHSUNDEEP
excelexcel-2010
alias51
alias51

Reputation: 8608

How to test if cell contains specific text in formula

Consider the following formula for cell A1

=Assumptions!$F$25*Assumptions!Q25

How can I create a boolean function to test if A1 has a reference to the sheet Assumptions?

I know if this was simply a constant, I could use: =+ISNUMBER(SEARCH("assumptions",A1) but this doesn't work if the cell value is a formula.

Note I am using EXCEL 2010, so isformula() won't work

Thanks

Upvotes: 1

Views: 563

Answers (1)

JasonAizkalns
JasonAizkalns

Reputation: 20463

What about two alternative approaches?

  1. Do a find a replace on the = sign. This will force all of your formulas to text, then you can write a normal formula to do the SEARCH or FIND. Then reverse the formulas back. I usually do a find all = and replace with ^^^ - then just reverse with find all ^^^ and replace with =.

  2. If the heart of your question is just to find the references, why not use "find" CTRL+F ensure that Look in: Formulas is elected (under Options...), and then use the Find All button. This provides a nice list of output.

Upvotes: 2

Related Questions