Reputation: 33
is there a way to catch all cells containing #REF!
in their functions (not in their result), preferably without using vba?
example: i would like to catch cells containing e.g. this:
=+SUBSTITUTE("#REF!","#REF!","text")
1) since the displayed result of the cell is "text", which is not an error, =+ISERROR()
won't work.
2) i have also tried =+SUMPRODUCT(ISNUMBER(SEARCH("*ref*",A:A))*1)
but that doesn't work either.
3) the only thing i was able to achieve, was to identify the first cell in a column containing "#" with the use of =+MATCH("#",A:A,-1)
but that's only half of the answer since i can't/ do know how (?) to use it over several columns.
4) last but not least, i'm on excel 2010, so no =ISFORMULA()
available.
any ideas/ help will be greatly appreciated! thanks!
Upvotes: 2
Views: 165
Reputation: 1000
How about find & replace and substituting "=GetData"
with "^^GetData"
(replacing the =
with some character(s) which don't exist in your formulae)? Then you can use SEARCH
on it and once you're done you replace back "^^"
with "="
.
Upvotes: 2