I've been using the function below that I found in another post. However, I
am having a problem that if I have another workbook open at the same time,
then it sometimes grabs data from Sheets(n-1) from that workbook. I added
Application.Volatile to the function to have the function get the new data as
soon as the pointed to cell changes. Could this be what is causing the
workbook confusion?

Function PrevSheet(rg As Range)
n = Application.Caller.Parent.Index
If n = 1 Then
PrevSheet = CVErr(xlErrRef)
ElseIf TypeName(Sheets(n - 1)) = "Chart" Then
PrevSheet = CVErr(xlErrNA)
Else
PrevSheet = Sheets(n - 1).Range(rg.Address).Value
End If
End Function