Found a solution via a
vb guru I know.
1. create a custom function in
vb to convert all the formulas in a
given range into one long text string
Function ConcatFormulas(InputRange As Range)
' this function works to get all the formulas in a specified range,
' then concatenate their text together into one string
' you can then use the FIND function on the string to check for
problems.
Dim i As Integer
For i = 1 To InputRange.Cells.Count
ConcatFormulas = ConcatFormulas & InputRange(i).Formula
Next i
End Function
2. you can then call the function in the spreadsheet e.g.
=ConcatFormulas(A1:Z21)
3. run a find on the result to look for #REF!
=IF((ISERROR(FIND("#REF!",ConcatFormulas(A1:Z1)))) ,"ok","doh!")
The above basically says, merge all the formulas into one string, then
try and find the characters #REF!. if excel can't find an instance of
#REF!, everything is Ok.