Forum Discussion
Formula Help
Before I go away for the week, Carl, I wanted to add just one more word of encouragement to you here. You're clearly involved in facility maintenance in some way, so I'm sure you are well acquainted with the value of preventing problems in the first place. It's very much the case that fixing things can be--often if not usually--far more expensive than preventing the problem in the first place. We have all kinds of folk sayings about this truism:
- An ounce of prevention is worth a pound of cure...
- Don't wait to close the barn door until after the horse has run... (multiple variations on that one)
All of our suggestions are not meant to deny the need you have, and I hope we can get it done. I do hope you see this attempt, though, as comparable to closing the barn door after the damage (the ambiguity, in this case) has been done.
You (your organization) would be better served by fixing the processes by which these word orders are created in the first place, so that LHS situations are red-flagged right at the start, as they're written up. Whoever the software vendor is that created the basic system should be eager to make it more effective, for you as well as for other clients.
JMB17
Thank you John for your words of wisdom and encouragement. My job is one of oversight of the company that actually manages the properties. We do not monitor the work orders as they come into the managing company, we pour thru reports and such on a monthly basis to see how they are doing and report to them what Work Orders were deemed LHS. As you have seen on the Work Order Example I sent, there is a column, Column E, that specifies Priority. One of our jobs is to Monitor the applied Priority and to determine if the Work Order was or was not considered an LHS Work Order. So, this being said, you can see that our roll sits outside the day to day workings of the managing company. You could say we are Performance Evaluation Company meant to monitor various aspects of the managing company's operation. We review reports, have meetings with and report to the managing company how they are doing according to their governing documents. This is why as I have stated we don't need PERFECT, we just need a way to blast thru the numerous amounts of Work Orders looking for key words that would give us an idea of what Work Orders may be considered LHS. But yes, we realize we will still have go thru the Work Order reports manually to verify otherwise. I am not apposed to a VBA Solution or an applied Formula to each report as we get it. I am just looking for a solution that will help me zip thru the report to give me an initial heads up on potential LHS issues. I really like the idea of the Table Solution you presented to me initially as its something we could build on to better improve the results. The idea is to TAG each Work Order with LHS or NLHS by inserting a column at the beginning or end of the report. The assignment of the TAG, LHS or NLHS may not always be correct but that's ok. Like I said, I'm not looking for PERFECT, just best guess. I would hope we could see this thru so I end up with some kind of Solution. Thank you and any other contributor's for helping me with this.
Give this file a try. Put the reports you want to process in their own folder, then click the button on the worksheet, which will open a dialog box for you to select your folder. Then, it should go through the files and find the category/subcategory/description/LHS fields (inserting a column for the LHS field if it does not find it as I assume that is not a field that is standard for the report - also, I'm assuming the other field header names are standard). And then, going through the descriptions, plugging them into the worksheet, getting the formula result and putting LHS or NLHS in the LHS field. Then, saving the file (which could be changed to a SaveAs if you don't want to overwrite the existing file).
Attached is a copy of the Category and Sub-category list. The items highlighted is Red are the Categories & the items listed under each category are the Sub-Categories. In the creation of Work Orders both Category & Sub-category must be selected. I am old that if you select "Appliance" for instance that the only viable Sub-category selections are those listed under Appliance. This holds true for each Category & Sub-category. Wanted to send this to you to see if it helps. Myself and my team will other wise build the Table for each Category & Sub-Category in order for this process to evolve.
- So it looks like I've been able to enable the Macros and I've created a folder on my Desktop called Work Order Reports. I've placed a work order report into the folder. I opened the folder to ensure the Work Order Report is in there and it is. When I click the Process Folder Button the dialog box opens and I can open the Work Order Reports folder but nothing is shown as being in the folder. I don't now what's up with that so I can't select the Report I want to nor can I see anything in the Folder. Please help! I'm excited about this. I may need some explanation on the building of the table and what is going on there. But my sincere thanks for this help.
- Sorry - I thought you might have multiple reports, so it's set up to process all of the files in a folder. But, I can modify it to select/process one file when you click the button.
- I forgot to ask. Sense my system is Disabling the Macros could you just paste the macro to a word doc and attach the word doc to this system. I’ll copy and paste the Macro accordingly. If I need help with this process I’ll reach out to you.
