Monthly list of accounts receivable

Excelsius3

New Member
Joined
Mar 8, 2018
Messages
4
Hello,

I am here because after an exhaustive search of the internet, I simply cannot find a solution to an Excel problem I have. Here's the situation. I have my own accounting spreadsheet that I have created myself. I have separate sheets for a lot of things, but two of them are income and gross receipts. Until recently, I only had to report and pay my gross receipts taxes semi-annually, so my sheet that calculates the gross receipts tax just copied the income data from the income sheet, performed the calculations, and totaled the tax for two six month periods. Since I didn't know how many receipts I would have in a six month period, I made plenty of room for them for each six-month period. Then, at the end of six months, I would adjust the beginning of the second six-month tabulation to start where the last receipt left off.

Now, I have to report monthly. So, I don't want to have to make that adjustment manually every month on my gross receipts sheet. What I want Excel to do is: identify, in order, all the receipts for each month, as they are entered on the income sheet, and place them, in order, in the correct month on the gross receipts sheet. So, as the year goes on, when the month changes and I receive another payment and I enter it on the income sheet, the gross receipts sheet automatically recognizes the month change and copies the information to the first row of the new current month. I have allotted six rows for each months receipts. If that heavenly day ever comes that I have more than that coming in, well, I'll deal with it then!

Clear as mud, right??? :laugh:

I have attached screenshots of the existing setup and as you can see, I am currently just manually identifying the row on the income sheet that starts the new month. Is what I am trying to do even possible? I have spent many, many hours trying various methods including the IF function, COUNTIF function, INDEX/MATCH functions, LOOKUP functions, ROW function, SMALL function, and so on. and many combinations thereof. Ack! :eeek:

Would love to solve this, but I admit, I'm not an advanced Excel user. That's for sure!

Many thanks, in advance, to anyone who can help me.

Best,

D
 
Last edited by a moderator:

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Well, I'm using a Mac with MS Office 2016. I had cut and pasted the images from Preview into my message, but I guess that doesn't work. I looked at the four options in jtakw's post, and I don't think I'm saavy enough to figure those out, soooo, maybe a dropbox link to the files for anyone interested in seeing them? I hope I'm not breaking any forum rules.

https://www.dropbox.com/sh/1tg7rlu7z7wz4ib/AADBnMn9zvgGTkm-hPWZuMK1a?dl=0

D
 
Upvote 0
Okay, so I got the table that calculates the tax, the target table so to speak, more or less, then below that, the table where the data come from.

January
Invoice # and Client Name Invoice Total Invoice (-GRT) TTL Non-tax TTL Taxable TTL Tax CHKSUM Santa Fe GR
PE-18-01 Client #1 $2,277.19 $2,100.00 $0.00 $2,100.00 $177.19 $0.00 $2,100.00
PE-17-30 Client #2 $5,325.00 $5,325.00 $5,325.00 $0.00 $0.00 $0.00 $5,325.00
PE-17-36 Client #2 $2,770.00 $2,770.00 $2,770.00 $0.00 $0.00 $0.00 $2,770.00
$0.00 $0.00 $0.00
$0.00 $0.00 $0.00
$0.00 $0.00 $0.00
Totals: $10,372.19 $10,195.00 $8,095.00 $2,100.00 $177.19 $0.00 $10,195.00

February
PE-18-02 Client #1 $967.12 $900.00 $0.00 $900.00 $67.12 $0.00 $0.00
PE-17-42 Client #3 $5,825.00 $5,825.00 $5,825.00 $0.00 $0.00 $0.00 $5,825.00
PE-17-40 Client #2 $1,950.00 $1,950.00 $1,950.00 $0.00 $0.00 $0.00 $1,950.00
PE-18-03 Client #1 $2,765.16 $2,550.00 $0.00 $2,550.00 $215.16 $0.00 $2,550.00
$0.00 $0.00 $0.00 $0.00 $0.00 $0.00
$0.00 $0.00 $0.00 $0.00 $0.00 $0.00
Totals: $11,507.28 $11,225.00 $7,775.00 $3,450.00 $282.28 $0.00 $10,325.00

March
PE-17-43 Client #2 $2,850.00 $2,850.00 $2,850.00 $0.00 $0.00 $0.00 $2,850.00
0 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00
0 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00
0 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00
0 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00
0 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00
Totals: $2,850.00 $2,850.00 $2,850.00 $0.00 $0.00 $0.00 $2,850.00

April
0 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00
0 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00
0 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00
0 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00


Invoice # & Client Name Date Debit Credit Balance
Beginning Balance $0.00
PE-18-01 Client #1 1/22/18 $2,277.19 $2,277.19
PE-17-30 Client #2 1/25/18 $5,325.00 $7,602.19
PE-17-36 Client #2 1/26/18 $2,770.00 $10,372.19
PE-18-02 Client #1 2/13/18 $967.12 $11,339.31
PE-17-42 Client #3 2/22/18 $5,825.00 $17,164.31
PE-17-40 Client #2 2/23/18 $1,950.00 $19,114.31
PE-18-03 Client #1 2/24/18 $2,765.16 $21,879.47
PE-17-43 Client #2 3/1/18 $2,850.00 $24,729.47
$-
$-
$-
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,971
Members
452,371
Latest member
Frana

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