Forum Discussion
Formula Help
Carl, let's assume you do figure it out, and then pass on the spreadsheet to somebody else, and the time comes when that somebody else needs to make a modification. Do you think they'd be able to figure it out?
What I'm getting at is that this formula is almost a case-study in formulas that should not be written as a single formula. Most text books on Excel advise specifically avoiding deeply nested IF functions for precisely the reason that you're encountering: they're hard to create in the first place, even harder to decipher.
The help I'd offer is this: break the various test conditions apart and deal with such things as different appliances separately. Don't try to write a single master function that covers the universe of different problems or conditions that could arise.
Another alternative is to create a table of conditions and use XLOOKUP or INDEX and MATCH to find the right combination of conditions.
What's the array of possible data that will appear in cells E4, F4, and G4. What cell is this formula itself in, for that matter? You don't explain what LHS is, for another thing, but these are just examples of how hard you're making it for anybody on the outside to help. If you can spell out in simple words what you're trying to do, the bigger picture, with an example or two in words, maybe somebody here could help.
- Hello John, Thank you for replying to my post. I work with a lots of work orders that I have to review each month for which I want to have a quick way to scan thru them looking for conditions that may be deemed LHS. This is Life, Health, Safety issues. Issues that may effect living conditions of the residents. A report is produced each month of all work orders and I need a quick way to blast thru the report line by line to determine if any of the work orders that have been submitted have any issues that could be deemed LHS. You actually started to help me with this is the not so long ago past but other issues came up that were of more importance so I set this to the side. I like your approach but will it be viable for my needs. Each work order has a Work Order Category, a Sub-category and a Work Order Description. The Work Order Description is a Free Form Comment Box where the description of the problem is entered. So as you see in my ISNUMBER(SEARCH() I am trying to create a list of possible conditions/words that we will I checking for and if found in the comment box made by the residents, the system will flag it as a possibly "LHS". So based on the Work Order Category and also the Sub-category, the items to be looked for in the Work Order Description cell will be different based on these items. Not sure however how to build this using VLOOKUP but not apposed to different approach.
- Is it possible, Carl, to attach a copy of the spreadsheet with all those work orders (so long as there's no proprietary info in it, no names of residents, etc).
It sounds, though, as if you're trying to do almost the impossible in searching and finding warnings signs in a free form comment box...to be reliable that would take artificial intelligence.
Who generates these work orders? Are they entering them into a computer? Could there be dropdown boxes that cover the LHS conditions? In short, is there a way to get the input more refined/defined to begin with?- The answer to that question is yes. All work orders are entered into a computer. There is a monthly report that comes out where by we check the report for that which is being claimed to be LHS issues. Our job is to sort thru the report look to verify or disclaim items being identified as LHS. I know there is no way to be 100% accurate on this but we want to have a way to blast thru lines of work orders with some kind of reasonable reliability. Right now we have to look thru these reports which can be hundreds of lines of work orders with our eyes and it takes up a bunch of time. Determining if a work order is LHS is subjective but we are trying to create a standard we all can rely on.
