VB & VBA in a nutshell
By Paul Lomax
http://www.amazon.com/exec/obidos/t...102-0145038-0419327?v=glance&s=books&n=507846
By Paul Lomax
http://www.amazon.com/exec/obidos/t...102-0145038-0419327?v=glance&s=books&n=507846
[A good cause. Long.] Need help from Excel gurus
- Thread starter Heretic Apostate
- Start date
Heretic Apostate
First Post
ARGH!!!!!!!!!!!!
There are times when I HATE Excel...
I'm working on the specific breakouts. It involves an advanced filter, the same type of advanced filter I used successfully ten other times already.
And it won't work. I have no idea why! The advanced filter command follows the exact same format as the others, but no matter how many times I try it, it won't work.
I even set it up in another workbook, on the assumption that it might exceed the number of filters at once. But that's not it.
Argh! Argh! Argh! I wouldn't be so upset if I couldn't get the Detail ones to work, those only I really used. But this, this is one of the things that impressed the heck out of my ex-coworkers...
Oh well, I'm going back to banging my head, trying to figure out why it ain't working...
There are times when I HATE Excel...
I'm working on the specific breakouts. It involves an advanced filter, the same type of advanced filter I used successfully ten other times already.
And it won't work. I have no idea why! The advanced filter command follows the exact same format as the others, but no matter how many times I try it, it won't work.
I even set it up in another workbook, on the assumption that it might exceed the number of filters at once. But that's not it.
Argh! Argh! Argh! I wouldn't be so upset if I couldn't get the Detail ones to work, those only I really used. But this, this is one of the things that impressed the heck out of my ex-coworkers...
Oh well, I'm going back to banging my head, trying to figure out why it ain't working...
Heretic Apostate
First Post
Okay, found out what it was.
When filtering, and you have variable criteria, make sure that you DON'T use the "" with number fields when trying to specify no criteria.
I have the specific breakouts set up so that the user can specify what they want to search for. For instance, they can search for NEVR (and the spreadsheet would know to look in the Account Name field), or IRS (in the Source field). This wouldn't work with the Payee field, however, since it's numbers. So I had to set the null as ">0" instead of "".
Argh, that was a frustrating couple of hours.
When filtering, and you have variable criteria, make sure that you DON'T use the "" with number fields when trying to specify no criteria.
I have the specific breakouts set up so that the user can specify what they want to search for. For instance, they can search for NEVR (and the spreadsheet would know to look in the Account Name field), or IRS (in the Source field). This wouldn't work with the Payee field, however, since it's numbers. So I had to set the null as ">0" instead of "".
Argh, that was a frustrating couple of hours.
Heretic Apostate
First Post
The alpha version is done. I'm going to give it to the boss when she gets back from vacation tomorrow. I made extensive use of the views thing you told me about, so it was helpful. 
What can SQL do? Is it a part of Excel I could use? I've heard of SQL (mostly from trying to access a message board like ENWorld and getting that error ), but that's about all I know. If it's a part of Excel, and if it's faster and less cumbersome than the advanced filters, I'll redesign the spreadsheet again. I figure it's a year or so before they REALLY start hitting the slowdowns I got from stress-testing my old spreadsheet.
Basically, I need to be able to do the following, preferably at the same time:
There will be a table of data: Corrected date (with calculations off to the side), Date of collection, Receipt Number, Date of receipt, Amount, Source, Account Name, Payee, and Case #. None of them are unique items.
I need to be able to do subtotals by month.
For each month, I need FTB and IRS totals (Source).
For each month, I need NEVR, PERM (w/o Foster Care) and FOST (which is a subgroup of PERM) totals (Account Name), with the remainder going into Other. (Note that NEVR and PERM are the left-most four digits of an account name, but the Foster Care stuff tends to be in the middle or right of the account name, so I need to search within the account name.)
I need to allow the user to specify what limits they wish within Source, Account Name, Payee, and Case (Payee for multiple payees within one case; Case because you only do one case at a time).
I need to be able to pull out Receipt #, Date of Receipt, Amount, Source, Account Number, and Payee for all data within a particular month.
I need to be able to pull out all of the previous paragraph for just all the Other data within a particular month.
So is there some Excel database stuff that can do all of this, all at once?
I completed it, but it's still 2.37MB. About 60% of what I started with, and kind of clunky still. But then, it's an alpha.
What can SQL do? Is it a part of Excel I could use? I've heard of SQL (mostly from trying to access a message board like ENWorld and getting that error
), but that's about all I know. If it's a part of Excel, and if it's faster and less cumbersome than the advanced filters, I'll redesign the spreadsheet again. I figure it's a year or so before they REALLY start hitting the slowdowns I got from stress-testing my old spreadsheet.Basically, I need to be able to do the following, preferably at the same time:
There will be a table of data: Corrected date (with calculations off to the side), Date of collection, Receipt Number, Date of receipt, Amount, Source, Account Name, Payee, and Case #. None of them are unique items.
I need to be able to do subtotals by month.
For each month, I need FTB and IRS totals (Source).
For each month, I need NEVR, PERM (w/o Foster Care) and FOST (which is a subgroup of PERM) totals (Account Name), with the remainder going into Other. (Note that NEVR and PERM are the left-most four digits of an account name, but the Foster Care stuff tends to be in the middle or right of the account name, so I need to search within the account name.)
I need to allow the user to specify what limits they wish within Source, Account Name, Payee, and Case (Payee for multiple payees within one case; Case because you only do one case at a time).
I need to be able to pull out Receipt #, Date of Receipt, Amount, Source, Account Number, and Payee for all data within a particular month.
I need to be able to pull out all of the previous paragraph for just all the Other data within a particular month.
So is there some Excel database stuff that can do all of this, all at once?
I completed it, but it's still 2.37MB. About 60% of what I started with, and kind of clunky still. But then, it's an alpha.
Similar Threads
