crusader8463
New Member
- Joined
- Dec 4, 2008
- Messages
- 26
Hi all, I’m going to try and explain my predicament as simply as possible in hopes to get some help. What I’m doing is all the book work for my father’s business. He does service calls to change tires on tractor trailers. So what I’m taxed with, is to take the bills he has for each service call, input them all into excel for each individual person, then total which bills people have or have not paid him. Simple I’m sure, but I’m a total beginner to excel and for the life of me can’t figure out a template I can use to just keep plugging in the numbers from all these receipts.
What I need the sheet to do:
<!--[if !supportLists]-->1.<!--[endif]-->Rows containing; Costumers name, invoice number, date of service, subtotal, tax, total, and whether or not they have paid yet.
<!--[if !supportLists]-->2.<!--[endif]-->I then need to take that data and group it to see how much each person still owes, which individual bills they have paid, and which individual bills they have yet to pay.
<!--[if !supportLists]-->3.<!--[endif]-->Then take all that data from each separate bill statement, and make an account balance for each person he does work for.
I had already done this for over a year’s worth of work he did. But after all the data was in, for the life of me I couldn’t think of a way to do step 2-3. The problem is that on some jobs he does, the people will pay him upfront, in which case he marks paid on the bill. But other times they will send him a check in the mail 1-3+ months later and some times not for the full amount they owe. Now I have a list of all those checks he receives, but I can’t find a simple way to subtract those checks from what people still owe him.
I need a way to sub-total each customers bills, but still be able to add in payments, or future bills and payments that person may have. Each month he does more work, and sometimes for the same people, so I need to be able to just sit down, look at each bill, and just type in the data I need off it into each person’s separate account.
The only way I could think to do this, is to give each different customer he has their own work sheet. But this makes the file size huge and organizing totals a pain. Not to mention editing the data in individual accounts for future work/payments a pain.
Anyway, any and all help will be much appreciated. I’m really lost as to find a way to do this. I guess a simple way to sum up everything I said above, would be a pivot table that I can add future bills to, and subtract payments from each customer’s total.
What I need the sheet to do:
<!--[if !supportLists]-->1.<!--[endif]-->Rows containing; Costumers name, invoice number, date of service, subtotal, tax, total, and whether or not they have paid yet.
<!--[if !supportLists]-->2.<!--[endif]-->I then need to take that data and group it to see how much each person still owes, which individual bills they have paid, and which individual bills they have yet to pay.
<!--[if !supportLists]-->3.<!--[endif]-->Then take all that data from each separate bill statement, and make an account balance for each person he does work for.
I had already done this for over a year’s worth of work he did. But after all the data was in, for the life of me I couldn’t think of a way to do step 2-3. The problem is that on some jobs he does, the people will pay him upfront, in which case he marks paid on the bill. But other times they will send him a check in the mail 1-3+ months later and some times not for the full amount they owe. Now I have a list of all those checks he receives, but I can’t find a simple way to subtract those checks from what people still owe him.
I need a way to sub-total each customers bills, but still be able to add in payments, or future bills and payments that person may have. Each month he does more work, and sometimes for the same people, so I need to be able to just sit down, look at each bill, and just type in the data I need off it into each person’s separate account.
The only way I could think to do this, is to give each different customer he has their own work sheet. But this makes the file size huge and organizing totals a pain. Not to mention editing the data in individual accounts for future work/payments a pain.
Anyway, any and all help will be much appreciated. I’m really lost as to find a way to do this. I guess a simple way to sum up everything I said above, would be a pivot table that I can add future bills to, and subtract payments from each customer’s total.
