Hi,
Why do you need a function to calculate this day?
There are a multitude of reasons, not the least of which is it removes the risk of error which will prevent major inaccuracies in forecasting cash flow. The function will be included in the workbook template and so automatically populate the required row for each year. This will be a major timesaver when setting up the new financial year. Without sight of the real life workbook which contains a shedload of confidential information the full explanation would be way too long and convoluted for the forum. Please accept it as a given that this is what I consider the best way to deal with a number of situations.

The function (“PrevSheet()”) is heavily used throughout the real life workbook and is normally held in a workbook loaded as a user created add-in. This did not travel with the original example but seems to figure in the problem for some reason. I have therefore moved it to a separate module within the new example workbook where it exhibits the same anomalous behaviour.

It is used with a cell reference passed as a parameter. It gets the value of the quoted cell from the previous sheet and displays it or uses it in the current sheet. For example PrevSheet(M50) gets the value in the cell M50 on the previous sheet and displays it in the calling cell on the current sheet; see cell N4 on the May sheet.

The code for PrevSheet() was lifted of the web many years ago and tweaked to suit. It does not work with Excel 2016 however, producing circular references for some reason. I did put it up on the forum but got no response.

The link for that post is #N/A” Error Encountered When Copying Formulas in O365 Excel 2016 (excelforum.com)

I finally solved the problem by reverting to Excel 2013. I came to the conclusion PrevSheet() either exploits a bug in Excel 2013 or finds one in Excel 2016, I have not tried it in Excel 2019. It now looks as if it has come back to bite me in the bum since it seems to be part of this problem.

The function in question, now inappropriately, named FindCellRef() is supposed to look in the previous sheet for the value of the cell in column C that corresponds to the row containing the record for the receipt and from that calculate the date of the receipt for the current month.

In the example workbook this is the value in Cell C13 (23) on the Apr worksheet. It then adds 28 to that value and finally subtracts the number of days in the previous month to obtain the day for the current month. It should then display the value in the calling cell. In the example workbook this should be the value 21 on the May worksheet.

For some obscure reason when it executes line 72 it calls the PrevSheet function. See comment also on line 73. NB: In writing up the problem and verifying it I did find this behaviour inconsistent, as was the notification for a circular reference.

The following formula performs the above task correctly provided there is only one receipt due for the month in which it is called, it cannot, however, cope with two receipts in one month, which happens on one month of the year. Maybe it will help you tease out what is required of my function FindCellRef().

Formula: copy to clipboard 
=INDIRECT(((TEXT($B$4-1,"mmm"))&"!"&ADDRESS(MATCH("Receipt - Every four weeks",INDIRECT(TEXT($B$4-1,"mmm")&"!$F1:$F49"),0),3)))+28-DAY(EOMONTH($B$4,-1))
it is called from cell C15 on the May worksheet,

I could not find a way of modifying it to give a correct result when there were two receipts on one month. The formula just got way too complicated which is why I tried using VBA.

As a list of instructions this is what is required:
  1. Establish the current sheet (Line 42).
  2. Establish the number of days in the previous month (Line 44).
  3. Check that it is not the first sheet in the workbook (line 51).
  4. Activate the previous worksheet (Line 59). I have activated the worksheet rather than selected it as I read somewhere that “.Find” only works on the active worksheet.
  5. Search the cells in column F until it finds one containing the search string, in this case “Receipt” (Line 61).
  6. Check that the search string has been found (Line 62).
  7. Obtain the cell address for the search string (Line 64).
  8. Obtain the value of the corresponding cell in column C (Line 66). This is the day that the receipt was received in the previous month as an integer.
  9. Activate current worksheet (the one we started with) (Line 70).
  10. Calculate the day the receipt will be received in the current month (Line 71).
  11. Set the value of C24 (arbitrary choice, could be any cell in Column C) to the current receipt date Line 73). This is a debug line only and is not required once the function is working. It does, however, cause an unpredictable action in jumping to the PrevSheet() function which can cause all sorts of upset! It may or may not be relevant.
  12. Attempt to set the return value of FindCellRef to the current receipt date (Line 75).

Note: Code for detecting two receipts in one month is yet to be attempted.

I hope this helps, if it doesn’t then I am at a loss!