Reply to thread

Message: <blockquote data-quote="Heretic Apostate" data-source="post: 895878" data-attributes="member: 696">It's not as impressive as it sounds.  I just use a LOT of advanced filters.First advanced filter, I weed out all lines of data that don't have a payee (because, if the money didn't go out to the custodial parent or foster care, we don't want to count it), belong with this particular case (because one person can have many, many, many cases, unfortunately), and that match the source, account name, payee and case limits set by the user.After that, I've set it up so that the user has the option  to define what sort of audit they're running (e.g., for support, it can be child, family, spousal, or medical, or all of the above; for welfare type, it can be nonwelfare, welfare, or foster care, or all of the above).  That's filter numbers 2 and 3.  The main data table is after filter #3.Then, since sumifs and conditional sums can't handle searching within a portion of text string, but rather have to compare against the whole text string, I've done a filter to pull out just payments from state tax intercepts (#4), federal tax intercepts (#5), payments that are paid to the custodial parent (#6), payments that go to repaying welfare grants (#7), and payments that go to repaying foster care (#8).  All of these pull from filter #3.The first eight filters are all summarized in the Summary table.Then there are four filters (pulling from filter #3) that give specific amounts by month.  For instance, let's say you want to search for all arrears (back balance) payments.  Well, it's set up that you just type in "ARRS."  The vlookup table looks up ARRS, finds out that it's found in the Account Name field, and tells the advanced filter to only include lines of data that have ARRS in the Account Name.  Similarly, it's set up to look for payment sources (IRS, FTB (state tax), wage withholding, liens, bankruptcy, unemployment, disability, etc.) and payees (e.g., in a particular case, payee #1 might be the mother, but payee #2 might be the maternal grandmother, and payee #3 might be foster care).And, finally, there are the two detail advanced filters.So that's a total of, what?, 12 filters.  Each filter needs to be able to handle 1800 rows (I calculate that there's at most 1 page of data --12 lines-- per month, from October 2000 to December 2010; that's 1476 lines of data that have to be handled).  So while the amount of space used is incredible, the actual number of formulas is actually relatively small.I'm shooting for this spreadsheet to be about half the size of my previous one.  That one was about 5 megs.I'm rather proud of my first spreadsheet, and they couldn't live without it.  Not bad, considering I gave it to my ex-boss as a Christmas present, eh?Now, if only they'd share it with the other counties in California.  There are (or are going to be) 30 or so counties within California who use the computer program this is made for, all the smaller counties.  Imagine how much time it could save!**********To give a comparison to what was being done before my spreadsheet, consider that they'd go through the data five times for each month they're auditing (IRS intercepts, FTB intercepts, nonwelfare, welfare, and foster care).  When you have (as of now) 31 months since the system was set up, that can take quite a bit of time.  And all those calculations were done with a calculator, giving rise to quite a bit of data entry error.It didn't help that each receipt (for money collected) could not only go to multiple account names (e.g., some might go to nonwelfare arrears, some to nonwelfare interest on arrears, some might go to welfare current support, and so on), but due to rollovers, money for previous months, and money being backed out of one account and sent to another after the fact, it could look like it went to multiple months.Anyway, after weeks of hashing out the process with them, I figured out how to automate most of the researching of dates and calculation of totals.  Now you can see what I mean by reducing an eight-hour process to at most 15 minutes.It doesn't do anything for the archival system, however, nor the pre-computerized system.  The archival system contains data from September 1991 to September 2000, and there are strange laws which affect that data.  I can set up a spreadsheet to handle the data, but it would be just as manual as a calculator, and there's so much subjective interpretation it just wouldn't be worth it.What's a viewname, and how do you set it up?Edit:  fixed quote tag.</blockquote>

Verification