I have a tracking spreadsheet in my office for one specific workload. The program my agency uses to track this workload updates once per day, and it doesn't provide the ability to add notes about each individual case, or other useful things. So I created a spreadsheet with the hopes of providing a one-button update that would take the exported information from the agency program (in your clipboard), post it to my spreadsheet, format it, run all kind of lovely if-thens that would then give you (on another sheet) a list of cases that need to be worked and what to do with them.
I'm really trying to make it as one-button as possible because while I understand every bit of the update process, at some point I'm going to have to turn it over to other users in my office. I don't want to sound mean, but I want to dummy-proof this thing as much as possible. But am I trying to do too much here? At any given time there's about 700-1200 cases in our office in various states of completion, so there's a lot of data to manage.
Here's basically my steps (if there's way to slice some out, I'd love to hear it):
1. Post clipboard contents to a blank (hidden) sheet.
2. Copy values except the first row (which contains the agency program's headers that I don't need), paste to another sheet with a formatted table (labelled "New").
3. Look for unique values in one certain column (hell, I'll admit- it's SSNs) between my tracking sheet and the sheet "New".
4. Delete any rows with unique SSNs on my tracking sheet (as those cases didn't show up on the new data from the agency, they have dropped off our list to work).
5. Copy any rows with unique SSNs on the "New" sheet, and paste them into my tracking sheet (as these are new cases to work).
6. Sort both tables (tracker and new) by A-Z, and make sure the list of SSNs match between sheets (and throw an error to stop the process if they don't).
7. Copy a range of all the values that apply to the case from the new sheet, paste over that range on the tracker sheet (pay codes or addresses or something there may have changed).
8. Update an "Information Last Updated" header to today's date.
I'm testing these things out in steps (to be honest, as I learn how to do them), but in the end I'd like to make it a one-button process. Is this feasible, or am I wasting my time? I'd like to know if you folks have made these kind of multi-stage change-everything macros on a regular basis.
I do have another sheet in the workbook full of if-then formulas that refer to my tracking sheet (If this case is over X days old and its' status is Y, then show Z as the possible problem, etc). If it's ok to keep piling onto one macro, I'd rather have them as part of it as well.
I've attached a simplified sample sheet. There's a lot of hidden columns, because the data we pull from the agency has a lot of crap on it irrelevant to the job we need to do. There's a ton of work still left for me to do on it, but I'd just like to know before I do that the idea is feasible.
Bookmarks