I don't think it is correct yet. I feel embarrassed for not being able to communicate it well. But, since you are still trying, I will too. I'll be as specific as I can. Sorry for the length, but skip any narrative that isn't helpful. (There is a summary at the end in
RED if you want to see if that makes it click).
I have about 50 clients (hospitals, surgery centers, rehab centers). Some of these clients are serviced quarterly, some semi-annually and some annually. A very few are one time clients or infrequent clients. Most of my clients are seen quarterly for different areas that need testing.
Recently I created what I call a Master Database in Excel. It is a workbook with several worksheets (Account, Test Areas, Billing, Generic Ventilation, Generic Chemical, Patient Rooms and Isolation Rooms). There is also a hidden sheet with lists for formulas and drop down lists.
Because certain state and federal regulations require it, I will see my clients in the same month of each quarter. So, if I see ClientA in Jan, I would return in Apr, Jul and Oct of that year as well. The schedule for the next year would be the same. Most of the clients follow that same formula. If I only see ClientB semi-annually, it would still be in the same month position of the quarter (ie Jan, Jul). This is the same for almost all clients, no matter which months they are seen. It follows the same pattern for any regularly serviced client.
Now, when I go to see ClientA for the first time (say on Jan 10th), I want to open up the Master Database workbook and start filling in information in the appropriate sheets. Of course, the Master Database is just my template workbook I use for all my clients, so I am going to save it (after putting in the data) as "ClientA_Master_01.10.2009.xlsx" and save it to C:\EDT\2009\JAN. This workbook now contains the address, contact info, and any test data I read when I serviced them. When I go back the next time (say on Apr 7th), I will use that new file as my template for ClientA because it already has the account info, test areas and other data there. But, this time I will save the file as "ClientA_Master_04.07.2009.xlsx"and save it to C:\EDT\2009\MAY. This will happen for every client I service. I still have the master database file to start up any new client, since that file is a template.
After all that, the C:\EDT\2009 folder would have 12 months of folders each with different client's master workbooks for those months inside. For the most part month FEB's folder contents will look like MAY, AUG and NOV. JAN's folder contents will look a lot like APR, JUL and OCT. MAR's folder contents will look a lot like JUN, SEP and DEC. This is because I see mostly the same clients at about the same time every quarter. About the only difference you’ll see from folder to folder is the date part of the filename.
Now, say it's mid December 2009 and I am wanting to plan my calendar and route for January 2010. I have to know what is due in January 2010 for ClientA (and any other client). But, that information is in all the client workbooks (in the Test Areas sheet) I produced for the last 12 months.
Some clients I serviced in Jan of 2009 may have some annual test areas that are not due until Jan 2010. I need to know what client that is, the area being tested, the chemical, the charge, etc. (basically most of the info in the test areas sheet in a given row where column F for that row reads Jan-2010). That same client may have other areas that I serviced in Apr, Jul and Oct of 2009 that are also now due in Jan 2010. So, when I call them to schedule a day and time to come by, I'm going to need to know what and how much is due to be serviced for that client. And, I need to know that about everyone else I need to visit in Jan 2010.
Therefore, if I have multiple workbooks for clients ("ClientName_Master_MM.DD.YYYY.xlsx") saved in different month folders (JAN-DEC) in the location C:\EDT\YYYY, I need a macro that will go into the folder C:\EDT and search through all workbooks up to 12 months back (based on an input date...
Jan 2010). In other words, if I am searching for what work I need to do in Jan 2010, it would not have to search through workbooks older than "ClientName_Master_01.01.2009.xlsx".
I was thinking this could be mined from the
TEST AREAS sheet of every workbook in C:\EDT. Using the image below, I thought it would look through every workbook for a cell (pink) in column F that had a date of (in this example) Jan-2010. Then it would return all the pertinent info adjacent to that found cell (yellow) to what I think you refer to as a
SUMMARY WORKBOOK. That summary workbook would be titled "Monthly Due" for my purposes.
Excel Workbook |
---|
|
---|
| A | B | C | D | E | F | G | H | I |
---|
5 | | Account | Location | Hazardous Chemical | Current Date | Next Date | Next Charge | Yearly Schedule | |
---|
6 | | 0238A-1 | PROCEDURE ROOMS | AIRFLOWS (ROOM) | January 10, 2009 | Jan-2010 | $200.00 | ANNUAL | |
---|
7 | | 0238F-1 | SUBSTERILE 1-2 | FORMALDEHYDE | January 10, 2009 | Jan-2010 | $175.00 | ANNUAL | |
---|
8 | | 0238F-2 | SUBSTERILE 3-MINOR | FORMALDEHYDE | January 10, 2009 | Jan-2010 | $175.00 | ANNUAL | |
---|
9 | | 0238F-3 | PROCEDURE ROOM 2 | FORMALDEHYDE | January 10, 2009 | Jan-2010 | $175.00 | ANNUAL | |
---|
10 | | 0238HYD-1 | CLEAN UTILITY | HYDROGEN PEROXIDE | January 10, 2009 | Jul-2009 | $500.00 | SEMI-ANNUAL | |
---|
11 | | 0238MA-1 | ENTIRE FACILITY | MEDICAL PIPING SYS | January 10, 2009 | Jan-2010 | $500.00 | ANNUAL | |
---|
12 | | 0238N-1 | SURGERY ROOMS | NITROUS OXIDE | January 10, 2009 | Apr-2009 | $600.00 | QUARTERLY | |
---|
13 | | 0238PE-1 | CLEAN UTILITY | PERACETIC ACID | January 10, 2009 | Jan-2010 | $175.00 | ANNUAL | |
---|
14 | | 0238VOC-1 | DECONTAMINATION | VOLATILE ORGANIC COM | January 10, 2009 | Jan-2010 | $175.00 | ANNUAL | |
---|
15 | | 0238VOC-2 | PROCEDURE ROOMS 1 & 2 SUBS | VOLATILE ORGANIC COM | January 10, 2009 | Jan-2010 | $175.00 | ANNUAL | |
---|
|
---|
The pink cells are what the macro is looking for because I need to know what is due during the month of Jan 2010. It then copies the pink and yellow cells to the summary workbook called "Monthly Due" and closes all the workbooks. The result would look like the image below (or something close to it...without the highlighted cells). The image only shows two clients for space.
Excel Workbook |
---|
|
---|
| A | B | C | D | E | F | G | H |
---|
1 | | | | | | | | |
---|
2 | | Work for MONTH of | JAN | | | | | |
---|
3 | | Work for YEAR of | 2010 | | | | | |
---|
4 | | | | | | | | |
---|
5 | | Account | Facility Name | Location | Hazardous Chemical | Next Date | Next Charge | |
---|
6 | | 0238A-1 | ClientA | PROCEDURE ROOMS | AIRFLOWS (ROOM) | Jan-2010 | $200.00 | |
---|
7 | | 0238F-1 | ClientA | SUBSTERILE 1-2 | FORMALDEHYDE | Jan-2010 | $175.00 | |
---|
8 | | 0238F-2 | ClientA | SUBSTERILE 3-MINOR | FORMALDEHYDE | Jan-2010 | $175.00 | |
---|
9 | | 0238F-3 | ClientA | PROCEDURE ROOM 2 | FORMALDEHYDE | Jan-2010 | $175.00 | |
---|
10 | | 0238MA-1 | ClientA | ENTIRE FACILITY | MEDICAL PIPING SYS | Jan-2010 | $500.00 | |
---|
11 | | 0238PE-1 | ClientA | CLEAN UTILITY | PERACETIC ACID | Jan-2010 | $175.00 | |
---|
12 | | 0238VOC-1 | ClientA | DECONTAMINATION | VOLATILE ORGANIC COM | Jan-2010 | $175.00 | |
---|
13 | | 0238VOC-2 | ClientA | PROCEDURE ROOMS 1 & 2 SUBS | VOLATILE ORGANIC COM | Jan-2010 | $175.00 | |
---|
14 | | | | | | | | |
---|
15 | | 0240A-1 | ClientB | PROCEDURE ROOMS | AIRFLOWS (ROOM) | Jan-2010 | $150.00 | |
---|
16 | | 0240A-2 | ClientB | DECONTAMINATION | AIRFLOWS (ROOM) | Jan-2010 | $50.00 | |
---|
17 | | 0240A-3 | ClientB | ENDOSCOPY | AIRFLOWS (ROOM) | Jan-2010 | $100.00 | |
---|
18 | | 0240N-1 | ClientB | SURGERY ROOMS | NITROUS OXIDE | Jan-2010 | $600.00 | |
---|
|
---|
The Facility Name in Column C would come from the filename the data came from or it could come from cell C5 of the account sheet. An added bonus would be a city and state designation for each client so I could filter the monthly due info by city or state. That data is in cells C7 and E7 respectively in each workbook's account sheet.
So, in my mind, the “Monthly Due” workbook goes into C:\EDT via a macro and looks for all workbooks with a date in the filename going back 12 months from a particular month and year that the user can input each time.. It opens those workbooks and looks in Column F of the TEST AREAS sheet for a particular month and year. If it finds that month and year, it copies the adjacent data in columns B, C, D, F and G of the TEST AREAS sheet and the data in cells C5, C7 and E7 from the ACCOUNT sheet to the appropriate cells of the “Monthly Due” workbook. It also enters a blank row after each client’s data so it is easier to read. It then closes all the workbooks except the “Monthly Due” workbook. (The underlined is a late addition and WISH. I also added it so you would scream out loud and curse my name.) <o></o>
OK. I think that’s about as good as I can explain it. Sorry if I’ve caused you sleepless night, hives or any other psycho-somatic episodes. And again, you are an Excel Saint for sticking with me this far. Even if I don’t get a solution, you have humbled me with your patience and perseverance.