AhoyNC,
I have had a play and so far I cannot get the results that I require.no joy.
Using Xl2bb, I attach sheets called Invoice Payment Details, Invoices and Formula all of which exist in the same workbook.
I the Invoices sheet, I have put the column numbers to aid you.
In Invoice Payment Details
I Invoice Payment Details sheet, Column K onwards are the values I am expecting for January thru March.
Columns B and C are formatted as Number, Columns D to E are formatted as Currency.
In B3, I would expect a value of 3 as there are 3 Due Dates which fall in January 2021 from sheet called Invoices (Column F). (Rows, 11 - 13)
In C3, I would expect a value of 2 as there are 2 Paid Invoices which fall in January 2021 from sheet called Invoices (Column G). (Rows, 11, 12)
In D3, I would expect a value of £125.00 as indicated on sheet called Invoices (Column K). ROW 11
In E3, I would expect a value of £125.00 as paid by Cheque as indicated on sheet called Invoices (Column L). ROW 12
In F3, I would expect a value of £0.00 as no one has paid via BACS indicated on sheet called Invoices (Column M).
In B4, I would expect a value of 4 as there are 4 Due Dates which fall in February 2021 from sheet called Invoices (Column F). (Rows, 14, 17 - 19)
In C4, I would expect a value of 3 as there are 3 Paid Invoices which fall in February 2021 from sheet called Invoices (Column G). (Rows, 14, 18 - 19)
In D4, I would expect a value of £1900.00 as paid by Cash as indicated on sheet called Invoices (Column K). ROW 14 and Row 18
In E4, I would expect a value of £0.00 as no one has paid by Cheque as indicated on sheet called Invoices.
In F4, I would expect a value of £90.00 as no one has paid by BACS as indicated on sheet called Invoices.
In B5, I would expect a value of 4 as there are 4 Due Dates which fall in March 2021 from sheet called Invoices (Column F). (Rows, 15, 21 - 23)
In C5, I would expect a value of 5 as there are 5 Paid Invoices which fall in March 2021 from sheet called Invoices (Column G). (Rows, 13, 20 - 23)
In D5, I would expect a value of £200.00 as paid by Cash as indicated on sheet called Invoices (Column K). ROW 14
In E5, I would expect a value of £300.00 as paid by Cheque as indicated on sheet called Invoices.
In F5, I would expect a value of £2400.00 as paid by BACS as indicated on sheet called Invoices.
I also attach sheet called Invoice Issued 2021 for completeness.
2021 Yearly Club Accounts.xlsm |
---|
|
---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R |
---|
1 | Monthly Invoice payments for 2021 | | | | | | | | | | | | | | | | | |
---|
2 | Month | Invoices Due for payment in the month | Invoices paid in the month | Cash | Cheques | BACS | Totals | | | | Invoices Due for payment in the month | Invoices Due for payment in the month
Row Numbers | Invoices paid in the month | Invoices paid in the month
Row Numbers | Cash | Cheques | BACS | Totals |
---|
3 | January | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | | | | 3 | 11 -13 | 2 | 11 - 12 | £125.00 | £125.00 | £0.00 | £250.00 |
---|
4 | February | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | | | | 4 | 14, 17 - 19 | 3 | 14, 18 - 19 | £1,900.00 | £0.00 | £90.00 | £1,990.00 |
---|
5 | March | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | | | | 4 | 15, 21 - 23 | 5 | 13, 20 - 23 | £200.00 | £300.00 | £2,400.00 | £2,900.00 |
---|
|
---|
2021 Yearly Club Accounts.xlsm |
---|
|
---|
| B | C | D | E | F | G | H | I | J | K | L | M | N |
---|
9 | Invoice Number
Column B | Receipt/
Statement No
Column C | Invoice
Amount
Column D | Date
Invoiced
Column E | Due Date
Column F | Date
Paid
Column G | Name | Income
Source | Invoice Amout
Outstanding | Cash
Column K | Cheque
Column L | BACS
Column M | Posted Line Total |
---|
10 | | | £8,240.00 | | | | | | £3,100.00 | £2,225.00 | £425.00 | £2,490.00 | £0.00 |
---|
11 | 0001 | 123 | £125.00 | 04/01/21 | 18/01/21 | 20/01/21 | Company A | | £0.00 | £125.00 | | | £0.00 |
---|
12 | 0002 | 0 | £125.00 | 04/01/21 | 18/01/21 | 24/01/21 | Company B | | £0.00 | | £125.00 | | £0.00 |
---|
13 | 0003 | 0 | £1,500.00 | 04/01/21 | 31/01/21 | 31/03/21 | Company C (QTR 1) | | £0.00 | | | £1,500.00 | £0.00 |
---|
14 | 0004 | 0 | £1,500.00 | 04/01/21 | 28/02/21 | 28/02/21 | Company C (QTR 2) | | £0.00 | £1,500.00 | | | £0.00 |
---|
15 | 0005 | 0 | £1,500.00 | 04/01/21 | 31/03/21 | 01/04/21 | Company C (QTR 3) | | £1,500.00 | | | | £0.00 |
---|
16 | 0006 | 0 | £1,500.00 | 04/01/21 | 30/04/21 | 00/01/00 | Company C (QTR 4) | | £1,500.00 | | | | £0.00 |
---|
17 | 0007 | 0 | £90.00 | 10/02/21 | 12/02/21 | 00/01/00 | Company D | | £0.00 | | | £90.00 | £0.00 |
---|
18 | 0008 | 0 | £400.00 | 10/02/21 | 15/02/21 | 18/02/21 | Company E | | £0.00 | £400.00 | | | £0.00 |
---|
19 | 0009 | 0 | £100.00 | 20/02/21 | 25/02/21 | 25/02/21 | Company F | | £100.00 | | | | £0.00 |
---|
20 | 0010 | 0 | £200.00 | 20/02/21 | 20/05/21 | 01/03/21 | Company G | | £0.00 | | £200.00 | | £0.00 |
---|
21 | 0011 | 0 | £300.00 | 20/02/21 | 03/03/21 | 03/03/21 | Company H | | £0.00 | | | £300.00 | £0.00 |
---|
22 | 0012 | 0 | £400.00 | 01/03/21 | 03/03/21 | 03/03/21 | Company I | | £0.00 | | £100.00 | £300.00 | £0.00 |
---|
23 | 0013 | 0 | £500.00 | 04/03/21 | 10/03/21 | 10/03/21 | Company J | | £0.00 | £200.00 | | £300.00 | £0.00 |
---|
24 | 0014 | 0 | £0.00 | 10/03/21 | 00/01/00 | 00/01/00 | Company K | | £0.00 | | | | £0.00 |
---|
25 | 0015 | 0 | £0.00 | 05/04/21 | 00/01/00 | 00/01/00 | Company L | | £0.00 | | | | £0.00 |
---|
26 | 0016 | 0 | £0.00 | 05/04/21 | 00/01/00 | 00/01/00 | Company M | | £0.00 | | | | £0.00 |
---|
27 | 0017 | 0 | £0.00 | 05/04/21 | 00/01/00 | 00/01/00 | Company N | | £0.00 | | | | £0.00 |
---|
28 | 0018 | 0 | £0.00 | 05/04/21 | 00/01/00 | 00/01/00 | Company O | | £0.00 | | | | £0.00 |
---|
29 | 0019 | 0 | £0.00 | 28/04/21 | 05/05/21 | 00/01/00 | Company P | | £0.00 | | | | £0.00 |
---|
30 | 0020 | 0 | £0.00 | 00/01/00 | 00/01/00 | 00/01/00 | 0 | | £0.00 | | | | £0.00 |
---|
|
---|
Invoices Issued.xlsx |
---|
|
---|
| A | B | C | D | E | F | G | H | I | J |
---|
1 | Inv No | Year | Whom | Usage | Date Invoiced | Due Date | Date Paid | Income Amount | Expenses Amount | Receipt/
Statement Number |
---|
2 | 0001 | 2 | 3 | 4 | 5 | 6 | 7 | £8,240.00 | £0.00 | 10 |
---|
3 | 0001 | 0 | Company A | Coporate | 04/01/21 | 18/01/21 | 20/01/21 | £125.00 | | 123 |
---|
4 | 0002 | 0 | Company B | Coporate | 04/01/21 | 18/01/21 | 24/01/21 | £125.00 | | |
---|
5 | 0003 | 0 | Company C (QTR 1) | Car Park | 04/01/21 | 31/01/21 | 31/03/21 | £1,500.00 | | |
---|
6 | 0004 | 0 | Company C (QTR 2) | Car Park | 04/01/21 | 28/02/21 | 28/02/21 | £1,500.00 | | |
---|
7 | 0005 | 0 | Company C (QTR 3) | Car Park | 04/01/21 | 31/03/21 | 01/04/21 | £1,500.00 | | |
---|
8 | 0006 | 0 | Company C (QTR 4) | Car Park | 04/01/21 | 30/04/21 | | £1,500.00 | | |
---|
9 | 0007 | 0 | Company D | Ground Hire | 10/02/21 | 12/02/21 | | £90.00 | | |
---|
10 | 0008 | 0 | Company E | Nets | 10/02/21 | 15/02/21 | 18/02/21 | £400.00 | | |
---|
11 | 0009 | 0 | Company F | Nets | 20/02/21 | 25/02/21 | 25/02/21 | £100.00 | | |
---|
12 | 0010 | 0 | Company G | Nets | 20/02/21 | 20/05/21 | 01/03/21 | £200.00 | | |
---|
13 | 0011 | 0 | Company H | Nets | 20/02/21 | 03/03/21 | 03/03/21 | £300.00 | | |
---|
14 | 0012 | 0 | Company I | Nets | 01/03/21 | 03/03/21 | 03/03/21 | £400.00 | | |
---|
15 | 0013 | 0 | Company J | Nets | 04/03/21 | 10/03/21 | 10/03/21 | £500.00 | | |
---|
16 | 0014 | 0 | Company K | Nets | 10/03/21 | | | | | |
---|
17 | 0015 | 0 | Company L | Nets | 05/04/21 | | | | | |
---|
18 | 0016 | 0 | Company M | Nets | 05/04/21 | | | | | |
---|
19 | 0017 | 0 | Company N | Nets | 05/04/21 | | | | | |
---|
20 | 0018 | 0 | Company O | Nets | 05/04/21 | | | | | |
---|
21 | 0019 | 0 | Company P | Ground Hire | 28/04/21 | 05/05/21 | | | | |
---|
|
---|
I hope that the above will help.