Hi Patricia,
The solution I provided for jammastajc was based on a file he emailed me. While the file was made somewhat "generic", I don't want to breach confidentiality by sending the file.
I'm certainly prepared to share my "workaround", which involves use of Excel's relatively unused but POWERFUL "Advanced Filter" component. But because each application is usually unique, it's usually best if the actual file is emailed - or a scaled-down version (with any sensitive data replaced with fictitious data that still represents the type of data you're working with).
Unfortunately Microsoft has not provided much in the way of support for the Advanced Filter component. My "best guess" as to the reasoning is because they would much prefer that users opt for purchasing Access (more $$$). While Access is preferable for some applications, there are MANY other "database" type applications that can be handled with a spreadsheet-based database - i.e. within Excel.
I can't expect to "write a book" within Tek-Tips". Therefore, here's a little information on the use of Excel's "Advanced Filter" component. It includes the ability to extract data (from a spreadsheet-based database) to a separate worksheet, or alternatively filter-in-place, based on any type of "criteria" you specify. The criteria can be simple, or as complex as is required to identify the precise data you require.
The same criteria can be used for database formulas (i.e. =DSUM, =DCOUNTA, =DMAX, =DMIN, etc) - to create a summary of totals, for example a matrix of data: by EMPLOYEE by YEAR, by DEPARTMENT by MONTH, etc.
I highly recommend that a SEPARATE sheet be used for creating the criteria. The reason is because Excel "gets confused" and will cause problems if the criteria is placed on the same sheet as the database.
The "smallest" criteria needs to consist of 2 cells - the top cell is for the field name, and the cell below is for the criteria. With text being used as the criteria, the field name is required. However, if a formula is used as the criteria, it's necessary that a field name NOT be used. But you must still reference BOTH cells as the criteria range - i.e. including the BLANK cell where the field name normally would be. Often a formula references a field name.
Here's an example where a formula is used in the criteria, and it's purpose is to isolate the records that fall within whatever MONTH is specified by the user. When the user specifies a month, the number of the month is placed in the cell named "mth" - see the CHOOSE formulas below.
=AND(Date>=INDIRECT(mo_s),Date<INDIRECT(mo_e))
"mo_s" is a range name for a cell containing the following formula...
=CHOOSE(mth,"jan_","feb_","mar_","apr_","may_","jun_","jul_","aug_","sep_","oct_","nov_","dec_"
"mo_e" is a range name for a cell containing the following formula...
=CHOOSE(mth,"feb_","mar_","apr_","may_","jun_","jul_","aug_","sep_","oct_","nov_","dec_","jan2_"
In these CHOOSE formulas, there is a reference to the different months - again by use of range names.
In the cell named "jan_", is the formula...
=DATE(year_,1,1)
In the cell named "feb_", is the formula...
=DATE(year_,2,1) ...etc.
The cell named "jan2_" has this formula...
=DATE(year_+1,1,1)
With this type of structure, changing from one year to another only involves changing the year in the cell named "year_".
That's probably getting close to some sort of maximum text allowed in Tek-Tips, so I'll end this "mini-course".
I M P O R T A N T P O I N T
There is one last point I'd like to make. I feel Excel users are REALLY being "short-changed" by Microsoft - by their continued refusal to "modernize" Excel to bring it up-to-date with the spreadsheet-based "database functionality" that existed over a decade ago.
Years ago when Microsoft created the filter/database component based on Lotus 123's pre-Windows version, they neglected to include all of the functionality of Lotus 123. The data manipulation functions Microsoft neglected to include are: Data-Find, Data-Modify, Data-Delete, and Data-Append.
Additionally, Lotus 123 had (spreadsheet-based) relational-database capability. So overall, Microsoft has a LONG way to go to make Excel as powerful as Lotus 123 was over 10 years ago. Once these missing functions are included, Excel users will be completely overwhelmed at "RAW POWER" that these additional functions provide will provide.
And finally, these "custom functions" are written in the same "C" language as Excel, and are therefore VERY fast - especially compared to the VBA "looping" routines that Excel users have had to resort to in place of the missing custom functions ...not to mention the "code execution has been interrupted" errors reported earlier in this thread.
I hope this has been useful reading, and that perhaps it will inspire some Excel users to take a serious look at Excel's "Advanced Filter". While the Advanced Filter component only includes the ability to: a) filter-in-place, or b) extract data to a separate sheet, there is STILL sufficient capability here to warrant its use. And of course there are also the database functions (=DSUM, etc).
I'm hopeful that the more Excel users begin to utilize this Advanced Filter component, the more "pressure" can be placed on Microsoft to FINALLY come through with the long-awaited upgrade to bring this component of Excel up to the same level as that other "ancient" spreadsheet.
Hope this helps.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca