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???
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!
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
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???
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!
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: