How do I trace which cell a #REF! error pointed to?

[Dan E]

I maintain some spreadsheets containing formulae like this:-

=IF(OR(AN135="",AN135="-"),"-",IF(RIGHT(AN135,2)="OR","-",LOOKUP(AN135,Crib!$A$1:$A$272,Crib!$B$1:$B$272))).

The cells containing the formulae are locked, and protection is normally
turned on. Most of the time, the spreadsheets work fine, but occasionally,
the user reports a #REF! error, and when I take a look, sure enough, somehow
one of these formulae has been replaced by a version that must (I guess) be
pointing to a non-valid cell. The error version appears like this -

=IF(OR(#REF!="",#REF!="-"),"-",IF(RIGHT(#REF!,2)="OR","-",LOOKUP(#REF!,Crib!$A$1:$A$272,Crib!$B$1:$B$272))).

Is there a way to find out what the #REF! was pointing to (in the case
above, it should be AN137)? Seems to be the only way I'm going to find out
why this happens. The sheet has a macro, that simply does some some
formatting and does some conditional testing on Either running a macro is
the cause, or it may be that something happens when the sheet is temporarily
unlocked.

All help or suggestions gratefully received and acknowledged!

TIA,

Dan

 

[Dave Peterson]

I think the only way to find out what it used to point at is by looking at a
backup copy (that hasn't been broken).

 

[Ken Wright]

Other than Dave's suggestion, are there perhaps similar formulas in the
cells next to it that would point you in the right direction.

 

[Dan E]

Dave, Ken - Fixing the error is the work of a moment, because there are,
surrounding the cell, many instances of good versions, and they're all
relative in this sheet, but I have no way of knowing how the bad version of
the formula got there. Once it's bad, it stays bad until I put a good
version in its place - I don't know of a way to wind the clock back and find
out what was there that caused the #REF!. I thought there might be some way
of going back to a point before the formula was transformed into one with
#REF!s by some action on the part of the user. I guess not, huh? I checked
an archived version, and the formula in that cell location was exactly as it
should have been. I know that something causes this, but have no clue as to
what it might be. There is a macro that the user runs, but it just does
some formatting based on schedule codes etc. - nothing I can see that could
do harm.

Any further suggestions very gladly accepted!

TIA,

Dan

 

[Dan E]

Ken - thanks for your input - see my reply to Dave.

Dan

 

[Ken Wright]

You generally get these errors because the cell that they referred to has
been deleted - not cleared, but physically deleted. eg if you have a formula
such as =A1+1 and you delete Col A, bingo #REF error. Check the code for
anything that looks like it will delete a range, or post it here and we can
take a look. Failing that it's probably down to a PICNIC error - (Problem
In Chair Not In Computer) - User may well be deleting cells/rows/columns
manually.

 

[Dave Peterson]

Or even worksheets.

=sheet2!a1

will work nicly until sheet2 is deleted.

 

[Dan E]

Thanks, Dave and Ken. User swears that no cell deletions have occurred, and
indeed there's no evidence that that happened. The sheet covers 2 weeks of
a schedule, and each day has 3 cells in a row (rows being a person), the 3
cells being in order - shift code, formula, formula, then that pattern
repeats. The surrounds of the bad formula show no evidence of cell, row or
column deletions, so it's weird.... The macro doesn't delete anything, just
changes attributes and/or values.

Thanks again,

Dan

 

[Dan E]

Thanks Dave - see reply to Ken.

Dan

Or even worksheets.

=sheet2!a1

will work nicly until sheet2 is deleted.

 

[Ken Wright]

Any references to named ranges where the names may have been deleted? A lot
easier to give suggestions if we can see the code - If nothing else it helps
us be sure we can discount it.

 

[Dan E]

Ken - no named ranges in the sheets. Here's the macro code - rather long
I'm afraid, and please remember I'm an amateur - most of this code was
suggested to me by very kind people on this newsgroup . It does these
jobs - 1) Colors cells containing specific shift codes; 2) Selects "active"
rows, where there's an X in column AT, and clears the color background from
empty cells in those rows; 3) Hunts for values of 1,2 or 3 in column W in
active rows (such codes denting where all shifts are covered by 2 workers,
providing continuity of care to patients), and calls subs to work out
continuity of care overtime (>40 hours per week) 4) calculates all
overtime:-
_______________
Sub Main_REHAB()
ActiveSheet.Unprotect
Color_Text
myRows
CC_OT
ALL_OT
ActiveSheet.Protect
End Sub

Sub Color_Text()
Dim cell As Range
Dim col As Integer
On Error GoTo ws_next
For Each cell In ActiveSheet.UsedRange
If cell.Interior.ColorIndex = 1 Or _
cell.Interior.ColorIndex = 15 Then
ElseIf Len(cell.Value) = 2 Or Len(cell.Value) = 3 Then
Select Case LCase(cell.Value)
Case "umr": col = 40
Case "ra": col = 38
Case "rb": col = 35
Case "rc": col = 36
Case "cs": col = 37
Case "rf": col = 38
Case "rg": col = 35
Case "rh": col = 36
Case "r1": col = 38
Case "r2": col = 35
Case "r3": col = 36
Case "r4": col = 24
Case "r5": col = 43
Case "r6": col = 22
Case "r8": col = 38
Case "r9": col = 35
Case "r10": col = 36
Case "eto": col = 0
Case Else: col = cell.Interior.ColorIndex
End Select
cell.Interior.ColorIndex = col
End If
ws_next:
Next
ws_exit:
End Sub

Sub myRows()

Dim oRow As Range
Dim cell As Range
On Error GoTo ws_next2
For Each oRow In ActiveSheet.UsedRange.Rows
If Cells(oRow.Row, "AT").Value = "X" Then
For Each cell In Cells(oRow.Row, "AT").EntireRow.Cells
If IsEmpty(cell.Value) Then
cell.Interior.ColorIndex = xlColorIndexNone
End If
Next cell
End If
ws_next2:
Next oRow

End Sub

Sub CC_OT()

Dim oRow As Range
Dim cell As Range

On Error GoTo ws_next3
For Each oRow In ActiveSheet.UsedRange.Rows
If Cells(oRow.Row, "AT").Value = "X" Then
If Cells(oRow.Row, "W").Value = "1" Then
Call week1(oRow)
ElseIf Cells(oRow.Row, "W").Value = "2" Then
Call week2(oRow)
ElseIf Cells(oRow.Row, "W").Value = "3" Then
Call bothweeks(oRow)
End If
End If
ws_next3:
Next oRow

End Sub

Sub week1(oRow As Range)
If Cells(oRow.Row, "AW").Value > 40 Then
Cells(oRow.Row, "BA").Value = Cells(oRow.Row, "AW").Value - 40
End If
End Sub

Sub week2(oRow As Range)

If Cells(oRow.Row, "AX").Value > 40 Then
Cells(oRow.Row, "BA").Value = Cells(oRow.Row, "AX").Value - 40
End If
End Sub

Sub bothweeks(oRow As Range)

Cells(oRow.Row, "BA").Value = 0
If Cells(oRow.Row, "AW").Value > 40 Then
Cells(oRow.Row, "BA").Value = Cells(oRow.Row, "AW").Value - 40
End If
If Cells(oRow.Row, "AX").Value > 40 Then
Cells(oRow.Row, "BA").Value = (Cells(oRow.Row, "BA").Value + _
(Cells(oRow.Row, "AX").Value - 40))
End If
End Sub

Sub ALL_OT()

Dim oRow As Range
Dim cell As Range

On Error GoTo ws_next4
For Each oRow In ActiveSheet.UsedRange.Rows
If Cells(oRow.Row, "AT").Value = "X" Then
Cells(oRow.Row, "BB").Value = 0
If Cells(oRow.Row, "AW").Value > 40 Then
Cells(oRow.Row, "BB").Value = Cells(oRow.Row, "AW").Value -
40
End If
If Cells(oRow.Row, "AX").Value > 40 Then
Cells(oRow.Row, "BB").Value = (Cells(oRow.Row, "BB").Value +
_
(Cells(oRow.Row, "AX").Value - 40))
End If
End If
ws_next4:
Next oRow

End Sub

 

[Dave Peterson]

What about the formula that now includes the #ref! error?

Do the users do anything to those cells? Or near those cells?

 

[Harlan Grove]

You generally get these errors because the cell that they referred to
has been deleted - not cleared, but physically deleted. eg if you have
a formula such as =A1+1 and you delete Col A, bingo #REF error. . . .

....

Not necessarily. User could also be CUTTING some other cell entirely and
pasting into the referenced cell. That'll produce #REF! too.

 

[Dan E]

Dave - all they do is enter or clear a shift code one or two spaces to the
left.

Dan

 

[Dan E]

Thanks, Harlan - good point.

Dan

...

Not necessarily. User could also be CUTTING some other cell entirely and
pasting into the referenced cell. That'll produce #REF! too.

 

[Ken Wright]

Very true - Had forgotten that.

 

[Ken Wright]

To be honest, discounting the macro should be a doddle, and there's nothing
I can see that immediately springs to mind, but just run it on a working
copy of the sheet and you'll soon see if it was that in any way. As soon as
you discount that, then whatever is left, no matter how improbable, or how
much they deny it..............

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

Dave - all they do is enter or clear a shift code one or two spaces to the
left.

Dan

 

[Ken Wright]

<snip>

Before I forget again

 

[Dan E]

Thanks, Ken - your inevitable conclusion reminds me of Poirot (or was it
Miss Marple, or maybe Holmes)....

Thanks again.

Dan

To be honest, discounting the macro should be a doddle, and there's
nothing
I can see that immediately springs to mind, but just run it on a working
copy of the sheet and you'll soon see if it was that in any way. As soon
as
you discount that, then whatever is left, no matter how improbable, or how
much they deny it..............

 

[Ken Wright]

LOL - You just listed 3 of my favourite programs, Poirot in fact being my
all time favourite.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

Thanks, Ken - your inevitable conclusion reminds me of Poirot (or was it
Miss Marple, or maybe Holmes)....

Thanks again.

Dan
"Ken Wright" <[email protected]> wrote in message

<snip>