I have a workbook with quite a few sheets, many having formulas that refer to
other sheets. Sometimes when I make changes to some of the cells in certain
sheets, I'll get #REF!'s on other sheets/cells. I'm thinking I'd like to have
a separate sheet that would show which sheets have #REF!'s., something like:

Sheet a! ok
Sheet b! Ref
Sheet c! Ref
Sheet d! ok

So I could easily goto that sheet and fix the #REF!.

Is that possible ?

Thanks,

Steve

Steve wrote...
....
a separate sheet that would show which sheets have #REF!'s., something like:

Sheet a! * *ok
Sheet b! * *Ref
Sheet c! * *Ref
Sheet d! * *ok
....

If you want to identify worksheets which have cells evaluating to
#REF!, then with the worksheet name in cell A3, you could use the
formula

=IF(COUNTIF(INDIRECT("'"&A3&"'!A:IV"),"=#REF!"),"R ef","ok")

Note that such formulas recalculate very slowly. Since you wouldn't
need to do this often, you may be better off using a macro to list the
worksheets with cells evaluating to #REF!. Something like


Sub foo()
Dim ws As Worksheet, wsc As Sheets, res As String

If ActiveWindow.SelectedSheets.Count 1 Then
Set wsc = ActiveWindow.SelectedSheets
Else
Set wsc = ActiveWindow.Parent.Worksheets
End If

On Error Resume Next
Debug.Print String$(40, "-")

For Each ws In wsc
Call ws.UsedRange _
.SpecialCells(xlCellTypeFormulas, xlErrors) _
.Find(what:="#REF!", LookIn:=xlValues)

If Err.Number = 0 Then
res = res & vbLf & ws.Name
Debug.Print ws.Name
Else
Err.Clear
End If
Next ws

On Error GoTo 0
Debug.Print String$(40, "-")

If res = "" Then res = "<none" Else res = Mid$(res, 2)
MsgBox Title:="Worksheets with #REF! errors", Prompt:=res,
Buttons:=vbOKOnly

End Sub

Keeping your output structure as is, couldn't you use this slightly simpler
For Each loop?

Sub Foo()
Dim WS As Worksheet, Res As String
Debug.Print String$(40, "-")
For Each WS In ActiveWindow.SelectedSheets
If Not WS.Cells.Find(What:="#REF!", LookIn:=xlValues) Is Nothing Then
Debug.Print WS.Name
Res = Res & vbLf & WS.Name
End If
Next
Debug.Print String$(40, "-")
If Res = "" Then Res = "<none" Else Res = Mid$(Res, 2)
MsgBox Res, vbOKOnly, "Worksheets with #REF! errors"
End Sub

--
Rick (MVP - Excel)



"Harlan Grove" wrote in message
...
Steve wrote...
...
a separate sheet that would show which sheets have #REF!'s., something
like:

Sheet a! ok
Sheet b! Ref
Sheet c! Ref
Sheet d! ok
...

If you want to identify worksheets which have cells evaluating to
#REF!, then with the worksheet name in cell A3, you could use the
formula

=IF(COUNTIF(INDIRECT("'"&A3&"'!A:IV"),"=#REF!"),"R ef","ok")

Note that such formulas recalculate very slowly. Since you wouldn't
need to do this often, you may be better off using a macro to list the
worksheets with cells evaluating to #REF!. Something like


Sub foo()
Dim ws As Worksheet, wsc As Sheets, res As String

If ActiveWindow.SelectedSheets.Count 1 Then
Set wsc = ActiveWindow.SelectedSheets
Else
Set wsc = ActiveWindow.Parent.Worksheets
End If

On Error Resume Next
Debug.Print String$(40, "-")

For Each ws In wsc
Call ws.UsedRange _
.SpecialCells(xlCellTypeFormulas, xlErrors) _
.Find(what:="#REF!", LookIn:=xlValues)

If Err.Number = 0 Then
res = res & vbLf & ws.Name
Debug.Print ws.Name
Else
Err.Clear
End If
Next ws

On Error GoTo 0
Debug.Print String$(40, "-")

If res = "" Then res = "<none" Else res = Mid$(res, 2)
MsgBox Title:="Worksheets with #REF! errors", Prompt:=res,
Buttons:=vbOKOnly

End Sub

Pretty slick. Thanks.
A few things:
I'm getting the hourglass hanging after running it.The only way to get rid
of it is to close the box or ESC.
Can the results be printed or copied to a section of a worksheet ?
It evalutas hidden sheets also. Is there a way to have it only look for
visible sheets ?
Is that a text reference to the "#ref!", meaning it's not only producing
results for the evaluated #Ref! but even if there happens to be #REF! as
text, correct ? Just curious.

Thanks again,

Steve
"Rick Rothstein" wrote:

Keeping your output structure as is, couldn't you use this slightly simpler
For Each loop?

Sub Foo()
Dim WS As Worksheet, Res As String
Debug.Print String$(40, "-")
For Each WS In ActiveWindow.SelectedSheets
If Not WS.Cells.Find(What:="#REF!", LookIn:=xlValues) Is Nothing Then
Debug.Print WS.Name
Res = Res & vbLf & WS.Name
End If
Next
Debug.Print String$(40, "-")
If Res = "" Then Res = "<none" Else Res = Mid$(Res, 2)
MsgBox Res, vbOKOnly, "Worksheets with #REF! errors"
End Sub

--
Rick (MVP - Excel)



"Harlan Grove" wrote in message
...
Steve wrote...
...
a separate sheet that would show which sheets have #REF!'s., something
like:

Sheet a! ok
Sheet b! Ref
Sheet c! Ref
Sheet d! ok
...

If you want to identify worksheets which have cells evaluating to
#REF!, then with the worksheet name in cell A3, you could use the
formula

=IF(COUNTIF(INDIRECT("'"&A3&"'!A:IV"),"=#REF!"),"R ef","ok")

Note that such formulas recalculate very slowly. Since you wouldn't
need to do this often, you may be better off using a macro to list the
worksheets with cells evaluating to #REF!. Something like


Sub foo()
Dim ws As Worksheet, wsc As Sheets, res As String

If ActiveWindow.SelectedSheets.Count 1 Then
Set wsc = ActiveWindow.SelectedSheets
Else
Set wsc = ActiveWindow.Parent.Worksheets
End If

On Error Resume Next
Debug.Print String$(40, "-")

For Each ws In wsc
Call ws.UsedRange _
.SpecialCells(xlCellTypeFormulas, xlErrors) _
.Find(what:="#REF!", LookIn:=xlValues)

If Err.Number = 0 Then
res = res & vbLf & ws.Name
Debug.Print ws.Name
Else
Err.Clear
End If
Next ws

On Error GoTo 0
Debug.Print String$(40, "-")

If res = "" Then res = "<none" Else res = Mid$(res, 2)
MsgBox Title:="Worksheets with #REF! errors", Prompt:=res,
Buttons:=vbOKOnly

End Sub

.

1) The hour glass cursor is what Excel does while it is displaying a
MessageBox... I'm pretty sure you can't turn it off except to dismiss the
MessageBox.

2) You can output the contents of the Res variable anywhere you need it.
Harlan used a MessageBox and I decided to use his same output structure in
my code, hence, I also used a MessageBox. To put it in a cell, say for
example purposes on Sheet1 in Cell A1, just change the last line of code to
this...

Worksheets("Sheet1").Range("A1").Value = Res

3) As the code was structured, it looked as Selected Sheets only... how did
you select a hidden sheet? I guess if you are running this through a loop of
all sheets, you could change this line in the If...Then statement...

WS.Cells.Find(What:="#REF!", LookIn:=xlValues)

to this...

WS.Cells.SpecialCells(xlCellTypeVisible).Find(What :="#REF!",LookIn:=xlValues)

4) Yes, depending on what your last Find settings were, it *might* find
#REF!, #Ref!, #ref!, etc. as a text string within cells displaying text
containing those characters. I say "might" because Find has a "feature"
whereby it remembers the settings previously assigned to its arguments. So,
if you had previously set the LookAt argument to xlPart, then it would find
the text #REF! within a larger piece of text containing it. Whether if would
find the other cases (#Ref!, #ref!, etc.) would depend on the previous
setting for the MatchCase argument. I assumed that such text would not
appear in your cell's normal text strings, so I didn't account for it. We
could control it somewhat be changing my Find function reference in the
If..Then statement to this...

Find(What:="#REF!", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)

This would still find "#REF!" as a text string IF your user decided to type
that into a cell or if you had any formulas that returned that exact text
string (which, personally, I think would be a silly thing to do). However,
if even this is a possibility, then you would need to go with Harlan's code
as he restricts his search to only those cells that returned an error.

--
Rick (MVP - Excel)



"Steve" wrote in message
...
Pretty slick. Thanks.
A few things:
I'm getting the hourglass hanging after running it.The only way to get rid
of it is to close the box or ESC.
Can the results be printed or copied to a section of a worksheet ?
It evalutas hidden sheets also. Is there a way to have it only look for
visible sheets ?
Is that a text reference to the "#ref!", meaning it's not only producing
results for the evaluated #Ref! but even if there happens to be #REF! as
text, correct ? Just curious.

Thanks again,

Steve
"Rick Rothstein" wrote:

Keeping your output structure as is, couldn't you use this slightly
simpler
For Each loop?

Sub Foo()
Dim WS As Worksheet, Res As String
Debug.Print String$(40, "-")
For Each WS In ActiveWindow.SelectedSheets
If Not WS.Cells.Find(What:="#REF!", LookIn:=xlValues) Is Nothing Then
Debug.Print WS.Name
Res = Res & vbLf & WS.Name
End If
Next
Debug.Print String$(40, "-")
If Res = "" Then Res = "<none" Else Res = Mid$(Res, 2)
MsgBox Res, vbOKOnly, "Worksheets with #REF! errors"
End Sub

--
Rick (MVP - Excel)



"Harlan Grove" wrote in message
...
Steve wrote...
...
a separate sheet that would show which sheets have #REF!'s., something
like:

Sheet a! ok
Sheet b! Ref
Sheet c! Ref
Sheet d! ok
...

If you want to identify worksheets which have cells evaluating to
#REF!, then with the worksheet name in cell A3, you could use the
formula

=IF(COUNTIF(INDIRECT("'"&A3&"'!A:IV"),"=#REF!"),"R ef","ok")

Note that such formulas recalculate very slowly. Since you wouldn't
need to do this often, you may be better off using a macro to list the
worksheets with cells evaluating to #REF!. Something like


Sub foo()
Dim ws As Worksheet, wsc As Sheets, res As String

If ActiveWindow.SelectedSheets.Count 1 Then
Set wsc = ActiveWindow.SelectedSheets
Else
Set wsc = ActiveWindow.Parent.Worksheets
End If

On Error Resume Next
Debug.Print String$(40, "-")

For Each ws In wsc
Call ws.UsedRange _
.SpecialCells(xlCellTypeFormulas, xlErrors) _
.Find(what:="#REF!", LookIn:=xlValues)

If Err.Number = 0 Then
res = res & vbLf & ws.Name
Debug.Print ws.Name
Else
Err.Clear
End If
Next ws

On Error GoTo 0
Debug.Print String$(40, "-")

If res = "" Then res = "<none" Else res = Mid$(res, 2)
MsgBox Title:="Worksheets with #REF! errors", Prompt:=res,
Buttons:=vbOKOnly

End Sub

.

Thank you very much for the very thorough explanations.
I just had those sheets hidden, but your suggested fix now looks at only the
visible sheets.

Two last things:
Is there a way to put the list starting in A1 thru, e.g. A20. This currently
is putting the entire list in just A1, causing Row 1 to be very, very tall.
Also, it seems to be listing sheets that do not have #REF! in them. I did
searches for them thinking it had #REF!s, but the search resulted in no
#REF!s being found.
I also double-checked via this from Cal =COUNTIF(Sheet2!A1:Z400,#REF!) and
that also is showing no #REF!s.

Thanks again,

Steve
"Rick Rothstein" wrote:

1) The hour glass cursor is what Excel does while it is displaying a
MessageBox... I'm pretty sure you can't turn it off except to dismiss the
MessageBox.

2) You can output the contents of the Res variable anywhere you need it.
Harlan used a MessageBox and I decided to use his same output structure in
my code, hence, I also used a MessageBox. To put it in a cell, say for
example purposes on Sheet1 in Cell A1, just change the last line of code to
this...

Worksheets("Sheet1").Range("A1").Value = Res

3) As the code was structured, it looked as Selected Sheets only... how did
you select a hidden sheet? I guess if you are running this through a loop of
all sheets, you could change this line in the If...Then statement...

WS.Cells.Find(What:="#REF!", LookIn:=xlValues)

to this...

WS.Cells.SpecialCells(xlCellTypeVisible).Find(What :="#REF!",LookIn:=xlValues)

4) Yes, depending on what your last Find settings were, it *might* find
#REF!, #Ref!, #ref!, etc. as a text string within cells displaying text
containing those characters. I say "might" because Find has a "feature"
whereby it remembers the settings previously assigned to its arguments. So,
if you had previously set the LookAt argument to xlPart, then it would find
the text #REF! within a larger piece of text containing it. Whether if would
find the other cases (#Ref!, #ref!, etc.) would depend on the previous
setting for the MatchCase argument. I assumed that such text would not
appear in your cell's normal text strings, so I didn't account for it. We
could control it somewhat be changing my Find function reference in the
If..Then statement to this...

Find(What:="#REF!", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)

This would still find "#REF!" as a text string IF your user decided to type
that into a cell or if you had any formulas that returned that exact text
string (which, personally, I think would be a silly thing to do). However,
if even this is a possibility, then you would need to go with Harlan's code
as he restricts his search to only those cells that returned an error.

--
Rick (MVP - Excel)



"Steve" wrote in message
...
Pretty slick. Thanks.
A few things:
I'm getting the hourglass hanging after running it.The only way to get rid
of it is to close the box or ESC.
Can the results be printed or copied to a section of a worksheet ?
It evalutas hidden sheets also. Is there a way to have it only look for
visible sheets ?
Is that a text reference to the "#ref!", meaning it's not only producing
results for the evaluated #Ref! but even if there happens to be #REF! as
text, correct ? Just curious.

Thanks again,

Steve
"Rick Rothstein" wrote:

Keeping your output structure as is, couldn't you use this slightly
simpler
For Each loop?

Sub Foo()
Dim WS As Worksheet, Res As String
Debug.Print String$(40, "-")
For Each WS In ActiveWindow.SelectedSheets
If Not WS.Cells.Find(What:="#REF!", LookIn:=xlValues) Is Nothing Then
Debug.Print WS.Name
Res = Res & vbLf & WS.Name
End If
Next
Debug.Print String$(40, "-")
If Res = "" Then Res = "<none" Else Res = Mid$(Res, 2)
MsgBox Res, vbOKOnly, "Worksheets with #REF! errors"
End Sub

--
Rick (MVP - Excel)



"Harlan Grove" wrote in message
...
Steve wrote...
...
a separate sheet that would show which sheets have #REF!'s., something
like:

Sheet a! ok
Sheet b! Ref
Sheet c! Ref
Sheet d! ok
...

If you want to identify worksheets which have cells evaluating to
#REF!, then with the worksheet name in cell A3, you could use the
formula

=IF(COUNTIF(INDIRECT("'"&A3&"'!A:IV"),"=#REF!"),"R ef","ok")

Note that such formulas recalculate very slowly. Since you wouldn't
need to do this often, you may be better off using a macro to list the
worksheets with cells evaluating to #REF!. Something like


Sub foo()
Dim ws As Worksheet, wsc As Sheets, res As String

If ActiveWindow.SelectedSheets.Count 1 Then
Set wsc = ActiveWindow.SelectedSheets
Else
Set wsc = ActiveWindow.Parent.Worksheets
End If

On Error Resume Next
Debug.Print String$(40, "-")

For Each ws In wsc
Call ws.UsedRange _
.SpecialCells(xlCellTypeFormulas, xlErrors) _
.Find(what:="#REF!", LookIn:=xlValues)

If Err.Number = 0 Then
res = res & vbLf & ws.Name
Debug.Print ws.Name
Else
Err.Clear
End If
Next ws

On Error GoTo 0
Debug.Print String$(40, "-")

If res = "" Then res = "<none" Else res = Mid$(res, 2)
MsgBox Title:="Worksheets with #REF! errors", Prompt:=res,
Buttons:=vbOKOnly

End Sub

.

.

I can't get the countif(indirect to work. It's producing a zero.

The macro is working, but it seems to only identify whether ref or not on
the sheet I'm on. I guess I have to go to each sheet and run the macro ?

Thanks,

Steve

"Harlan Grove" wrote:

Steve wrote...
....
a separate sheet that would show which sheets have #REF!'s., something like:

Sheet a! ok
Sheet b! Ref
Sheet c! Ref
Sheet d! ok
....

If you want to identify worksheets which have cells evaluating to
#REF!, then with the worksheet name in cell A3, you could use the
formula

=IF(COUNTIF(INDIRECT("'"&A3&"'!A:IV"),"=#REF!"),"R ef","ok")

Note that such formulas recalculate very slowly. Since you wouldn't
need to do this often, you may be better off using a macro to list the
worksheets with cells evaluating to #REF!. Something like


Sub foo()
Dim ws As Worksheet, wsc As Sheets, res As String

If ActiveWindow.SelectedSheets.Count 1 Then
Set wsc = ActiveWindow.SelectedSheets
Else
Set wsc = ActiveWindow.Parent.Worksheets
End If

On Error Resume Next
Debug.Print String$(40, "-")

For Each ws In wsc
Call ws.UsedRange _
.SpecialCells(xlCellTypeFormulas, xlErrors) _
.Find(what:="#REF!", LookIn:=xlValues)

If Err.Number = 0 Then
res = res & vbLf & ws.Name
Debug.Print ws.Name
Else
Err.Clear
End If
Next ws

On Error GoTo 0
Debug.Print String$(40, "-")

If res = "" Then res = "<none" Else res = Mid$(res, 2)
MsgBox Title:="Worksheets with #REF! errors", Prompt:=res,
Buttons:=vbOKOnly

End Sub
.