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

#### Tegglet

##### New Member
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:
Excel 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.

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:

ABCDEFGHIJKLMNO
1This is the First Sheet in the Workbook
2
3
401-04-2021This is the seed date for the start of the financial year.
5
6
7Apr Target 1A7 to A16 is the array that contains the target to be accessed from the May sheet
8Apr Target 2
9Apr Target 3
10Apr Target 4
11Apr Target 5
12Apr Target 6
13Apr Target 7
14Apr Target 8
15Apr Target 9
16Apr 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

ABCDEFGHIJKLMNOPQRS
1
2
3
401-05-2021This 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
8INDEX(Apr!A7:A16,5)Step 1Find target by entering the specific month and specific row number manually. Result = Apr Target 5
9INDEX(TEXT(EOMONTH(Apr!\$B\$4,0)-1,"mmm")!A7:A16,5)Step 2Attempt to enter the month using a formula that returns the name of the month as text - this fails, see snippet below
10INDEX(Apr!A7:A16,MATCH("Apr Target 5",Apr!A7:A16,0))Step 3Find target by entering the specific month manually and find the row number of the target using the "Match" function. Result = Apr Target 5
11INDEX(TEXT(EOMONTH(\$B\$4,0)-1,"mmm")!A7:A16,MATCH("Apr Target 5",EOMONTH(\$B\$4,0)-1A7:A16,0))Step 4Attempt 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
17TEXT(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
RangeFormula
B4B4=EOMONTH(Apr!B4,0)+1

#### Attachments

• Example sheet for Apr.JPG
57.2 KB · Views: 6
• Example sheet for May Showing Formulae.JPG
195.9 KB · Views: 6
• Error Dialogue Box.JPG
28.1 KB · Views: 6

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Replies
6
Views
190
Replies
2
Views
211
Replies
2
Views
323
Replies
1
Views
73
Replies
0
Views
53

1,181,598
Messages
5,930,808
Members
436,761
Latest member
mintwaxed

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back