Calculate the Date of an Item Based on the Date of a Similar Item in the Previous Month

Originally Posted by Tegglet

That returns a #VALUE error!

Sorry, the order of statements needs to change:

Public Function PrevSheet() As Variant

    Dim rCell As Range
    Dim wkbkP As Workbook
    Dim whstP As Worksheet

    Set rCell = Application.Caller
    Set whstP = rCell.Parent
    Set wkbkP = whstP.Parent
 

    Application.Volatile

    PrevSheet = wkbkP.Worksheets(whstP.Index - 1).Range(rCell.Address).Value

End Function

The lines "Dim rCell As Range" and "Set rCell = Application.Caller" are shown in red in your example; is that significant or just to bring my attention to the differences?

Only to show what I changed.

Regarding the argument not being required, how does it know which cell on the previous sheet contains the required value? For instance when used to get the previous months balance, the UDF entered in cell M4 needs the value in cell N50 on the previous sheet and the UDF in cell N4 needs the value in cell M50 on the previous sheet.

I was just trying to resolve the circular reference issue and focused on B4. I did not notice what was going on in those other cells. This version of the function assumes that if you call it from B4, you want B4 on the other sheet, and so on. If you call it from M4 but need to reference N50, then this will not work for you. Another option is to have it return the sheet name, then use an INDIRECT formula to reference the desired cell.