Forum Discussion
Formula Help
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.
- Ok Thank You. Usually its a monthly report so being able to select the report by drilling down to its location would be great. Otherwise, I like your thought on that. Again however, with the current process I could not even see the files in the folder. The system would not see past the Folder level. Also, could you give a little insight on the Table build and how that is working? Hopefully you have seen the Category & Sub-category report I attached last time.
- 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.
- Thank you, I downloaded the LHS.xlsm file, opened it and was told the Macros have been disabled. I clicked on Process Folder and the dialog box opened. I tried to select the file I wanted but I am not able to see beyond the folder levels. Any ideas and how do I enable the Macros that our system Disabled?