MS Excel spreadsheet

Factual Questions
1

I’m hoping someone might be able to help me design a better Excel spreadsheet for this task I have to perform weekly. I’ve probably made the whole task harder than it is but I don’t have any formal MS Excel training and so I’ve just picked up bits and pieces as I’ve gone along. I can learn after performing a task just once or twice, but I just don’t have the knowledge to use the Excel program effectively for this.

I have to request a good number of checks to be mailed out to various courts in two-three states each week. I don’t have a difficult time creating a workable format and easy spreadsheet for the checks that I request to be sent to the state of AL as their rules statewide are pretty uniform. However, the bulk of our business is in the state of GA, where every county gets to make up their own rules and it can get pretty ridiculous. It affects my forms as such.

There are four counties where I have to separate the totals into two checks - one for suits and reservice fees, one for garnishments. There are 3 other counties where we send the court fee to the magistrate office but the service checks to a private process server. I could drag this on but I think you get the point.
What I do now is start with my template which has a page for GA and a page for AL and one for AR. Now, it’s the GA page that gives me so much grief. On the GA page, I have several headings: Check, Date, County, Court, Name of Defendant, filing fee, garnishment fee, service fee and rule. I created 8 rules to sort the different courts in. Rule 1 is all courts that are merciful and allow us to send one check for everything. Rule 2 is for the example above where we have to write two checks. Rule 3 is for a county where we have to write three checks - one for suits, one for garns and one for the process server. Rule four is where we write one check for court and one for the private process server. It goes on from there - finally ending with Rule 7 - which is suits and garnishments I cannot count in our total because they are corrections. (We have a monthly quota which is currently at 500 and will be increasing in the next two months to 700. This requires that I actually know exactly how many go out each time and that I don’t count duplicates twice.)

Ok, so then after filling out this page, I create a pivot table and make it my master list. I then make a copy of the page for each rule and filter out the rules one by one. Then I have to go back because the pivot table separates the fees out and add them back together on the “Rule 1” page since it’s just one check per court. Because my boss is the one who writes the checks, I also have to go to each sheet and type at the bottom: “write two checks for Houston County SOC total: xxxx.00 Garn total: xxx.00” or “write two checks for Glynn County SOC total: xxxx.00 Garn total: xxx.00 and one check for the process server for xxx.00”

I just feel like there has to be a better way but I’m not experienced enough with the application to make it work for me in the best way possible. Can anyone help?

I’m sorry this is such a long and complicated post. I don’t know any other way to explain it other than to be very detailed. Any help or suggestions would be greatly appreciated!

2

Since there have been no replies I’d like to add that I would consider using another program if it would get the job done. Thanks for your time!

3

Read up on the “if, then” function. Works like, if you put a “4” into cell A2 then it will divide (or whatever you want) cell A3 by 2 (or whatever). Might work for splitting those checks in half. I’m a visual learner, so I get lost reading your post, but it just seem like you could eliminate some pivot tables and copying and all kind of stuff. There’s almost always a simpler way to do it in Excel.

4

I am familiar with “if, then” but I do not think it will not work in this case. I have to give my boss just a simple, printed sheet that lists all the checks he has to write. I know this is hard to understand the way I had to write it out. I considered maybe doing a screenshot of a sample listing to see if that makes things easier. I will try to do that this weekend.

5

Would it be possible and better to create a separate item (row) for every payment that needs to go out, then use Excel to aggregate them when you need to report total payments in any category (per account, per court, etc.)?

6

I don’t really mess with pivot tables, since I have other ways of processing data contained within a two-dimensional array, but I often construct usable text from an array based on the needs of a particular task, so I could probably help here, except I’m not sure I understand what constraints you have to work with. If it were possible for you to upload an example stripped of confidential data, that would help.

7

Yes, I can do this in a couple of hours. Do you want me to email it to you?