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

Tegglet

New Member
Joined
Feb 28, 2021
Messages
1
Office Version
  1. 2013
Platform
  1. Windows
  2. Mobile
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.

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
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

Example Spreadsheet to Upload to the Forum.xlsx
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
    Example sheet for Apr.JPG
    57.2 KB · Views: 2
  • Example sheet for May Showing Formulae.JPG
    Example sheet for May Showing Formulae.JPG
    195.9 KB · Views: 2
  • Error Dialogue Box.JPG
    Error Dialogue Box.JPG
    28.1 KB · Views: 2

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Watch MrExcel Video

Forum statistics

Threads
1,127,751
Messages
5,626,654
Members
416,198
Latest member
Enigma909

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top