How do I find "#REF" in how a function is actually written, not in what the cell displays?

[pmmille2]

Greetings:

First time on this forum. I'm glad this is here. Using 2003 on Windows 7 (I know, both are way behind the times, but they work for me!)

I need to find a way to locate the source of a #REF error. Until now I've always been able to scan my columns and find the cell with #REF right in how I've written the function and then fix it.

For the life of me, as I've been working with a multi-sheet workbook with a number of #REF errors, I cannot find the problem this time. Tracing is way too confusing, though I've really tried to use this to find precedents. It seems to take me in circles.

1. Is there a way to find "#REF " (without the quotation marks) in an actual function, rather than just in what the cell(s) displays? There should be a way but no advice I've read on the web addresses my question.

2. Is there another reason why I would have #REF errors other than a precedent cell having "#REF " in the function (as written) itself?

Thanks! Remember, I'm just an amateur, so be patient and assume nothing about my level of knowledge!

Thanks,
Paul

 

[jkpieterse]

If you click Find, expand the dialog by clicking Options. Under "Look in", select Formulas.

 

[footoo]

2. Is there another reason why I would have #REF errors other than a precedent cell having "#REF " in the function (as written) itself?

How to correct a #REF ! error - Office Support

#REF Excel Errors - How to Find and Fix #REF Errors

 

[pmmille2]

Thanks so much. I thought I'd tried using the Find option the way jkpieterse suggested, but all I can think of is that I didn't look on all of the worksheets I should have to find the error. All is well with the world again, or at least my spreadsheets!