I have a custom function (see below) that is used on several cells on
sheet 1. It all works fine except for when I make a copy of the sheet
into the same workbook and it calculates with some new numbers. What
happens is that sheet 1 and sheet 2 now show the same results. When I
recalc on the sheet that is wrong it messes up the other one. It seems
that when I watch the myRange.address as it recalculates all the
occurences of this function on sheet 1 & 2 it uses the same cell range
(from the active sheet) for all and this is why the other sheets do not
get their data calculated correctly.
So how do I make this custom function use the range from the sheet that
contains each of the cells that use this function?

Thanks for any help or direction you can give me.

Scott

Function LowestRepeatableNumber(myRange As Range)
Application.Volatile
'This function returns the second lowest number above zero if there are
no repeating numbers
LowestRepeatableNumber = Evaluate("MIN(IF(" & myRange.Address &
"0," & "IF(COUNTIF(" & myRange.Address & "," & myRange.Address &
")1," & _
myRange.Address & ")))")
If LowestRepeatableNumber = 0 Then LowestRepeatableNumber =
Evaluate("small(IF(" & myRange.Address & "0," & "IF(COUNTIF(" &
myRange.Address & "," & myRange.Address & ")=1," & _

myRange.Address & ")),2)")


End Function