Calculate the Date of a Receipt Based on the Date of the Receipt in the Previous Month

[Tegglet]

This is my first post on this forum so apologies for any mistakes.

I am using Excel 2013 for a cash flow spreadsheet to record income and expenditure. It uses one sheet per month for my current account and one sheet per month for my credit card account; 24 worksheets in total. To avoid tedious repetitive entries I try to create repeating events using formulae wherever possible which also reduces the risk of errors.

Note: I have remained with Excel 2013 because the spreadsheet uses VBA code which either exploits a bug in v2013 or exposes a bug in v2016. I have not tried anything later. The problem was posted on other forums some time ago but without a successful outcome.

I receive a regular income four weekly as well as other receipts.

I am having a specific problem creating the date for the four weekly receipts using functions.

I have attached mini sheet that I hope illustrates the problem clearly.

I am trying to calculate the receipt date in the current month based on the actual receipt date for the preceding month, the first payment in April is the seed entry and is entered manually.

I am trying to use the formula:

=INDEX(TEXT(EOMONTH(Apr!$B$4,0)-1,"mmm")!A7:A16,MATCH("Apr Target 5",Apr!A7:A16,0))

In real life this formula segment EOMONTH(Apr!$B$4,0)-1 is replaced by a custom VBA function and is the worksheet source date calculated for each monthly worksheet.

This works fine if I enter the month (sheet name) manually but try as I might, I cannot find a way of entering the name of the previous sheet using a function.

BTW: VLOOKUP is not suitable since it only looks to the right and I need a formula that will look to the left.

In the example spreadsheet:-
The first sheet, named Apr, contains the target data.

The second sheet, named May, contains the errant formulae for identifying the target cell in the preceding sheet and displaying its value. From this the date of the receipt for May can then be found by adding 28 days to the date.

There will be subsequent sheets created by copying May and renaming them to the appropriate month. The start date in cell B4 will be calculated automatically using the formula =EOMONTH(PrevSheet(B4),0)+1

Example sheet content and explanation:
The MATCH function used in Cells B10 and B11 contains the lookup value ("Apr Target 5") entered as a string and is constant throughout.

Cell B4 always contains the date for the first of the month in question i.e. 01-04-2021, 01-05-2021, 01-06-2021 etc and is used to find the name of the preceding month and hence the name of the preceding sheet. In real life it uses a bespoke function written in VBA =EOMONTH(PrevSheet(B4),0)+1. Apr!B4 is filled in manually with the year start date (01-04-2021).

Cell B8 contains the formula for finding the required Cell value on the preceding spreadsheet (Apr) with all data entered explicitly in the formula. It displays the correct result i.e. "Apr Target 5". In real life this would, of course, be an entry for the receipt in question.

Cell B9 contains the substituted formula (shown in red) for finding the previous month which is also the name of the previous sheet. The exclamation mark after the sheet name is added at this point. In real life this contains the bespoke function referenced above instead of Apr. This produces an error and clicking on the Fx icon produces the "Function Arguments" shown in the uploaded image.

Cell B10 contains the formula for finding the required Cell value using the MATCH() function with the sheet name entered manually. It displays the correct result.

Cell B11 contains the substituted formula (shown in red) for finding the previous month the same as in Cell B9 and produces the same result.

I have tried wrapping the formula for the sheet name with the INDIRECT function but get the same result.

This is the mini sheet for Apr which contains to target data:

Example Spreadsheet to Upload to the Forum.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1									This is the First Sheet in the Workbook
2
3
4		01-04-2021	This is the seed date for the start of the financial year.
5
6
7	Apr Target 1			A7 to A16 is the array that contains the target to be accessed from the May sheet
8	Apr Target 2
9	Apr Target 3
10	Apr Target 4
11	Apr Target 5
12	Apr Target 6
13	Apr Target 7
14	Apr Target 8
15	Apr Target 9
16	Apr Target 10
17
18
19
20
Apr

This is the mini sheet for May containing the errant formulae. The = prefix has been removed to show the formulae

Example Spreadsheet to Upload to the Forum.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2
3
4		01-05-2021		This is the seed date and is the first day of the month of the corresponding sheet and is derived from the previous sheet. In real life it uses a custom VBA function to access the previous sheet e.g. EOMONTH(PrevSheet(B4),0)+1
5
6
7
8		INDEX(Apr!A7:A16,5)			Step 1	Find target by entering the specific month and specific row number manually. Result = Apr Target 5
9		INDEX(TEXT(EOMONTH(Apr!$B$4,0)-1,"mmm")!A7:A16,5)			Step 2	Attempt to enter the month using a formula that returns the name of the month as text - this fails, see snippet below
10		INDEX(Apr!A7:A16,MATCH("Apr Target 5",Apr!A7:A16,0))			Step 3	Find target by entering the specific month manually and find the row number of the target using the "Match" function. Result = Apr Target 5
11		INDEX(TEXT(EOMONTH($B$4,0)-1,"mmm")!A7:A16,MATCH("Apr Target 5",EOMONTH($B$4,0)-1A7:A16,0))			Step 4	Attempt to enter the month using a formula that returns the name of the month as text and the the row number of the target using the "Match" function. This also fails for the same reason as Step 2
12
13
14
15
16
17		TEXT(EOMONTH(Apr!$B$4,0)-1,"mmm")			Formula used to derive the name of the previous sheet entering the sheet name explicity
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
May

Cell Formulas
Range	Formula
B4	B4	=EOMONTH(Apr!B4,0)+1