Forum Discussion
Formula Help
You might try putting your LHS conditions into a table and then using a formula to see if the values in E4, F4, and G4 exist in the table. Then, adding additional entries is just a matter of adding items to the table. Here is a description of how the formula is being used for a multiple condition test, so hopefully you will be able to follow it:
http://xldynamic.com/source/xld.SUMPRODUCT.html
As mathetes has also already said, this is not ideal. Ideally, whatever application or software you are using on the front end would have data validation so that the input was standard or would have some more reliable means of identifying LHS situations. But, if that's not an option, perhaps this will be somewhat helpful.
- Thank you for your response. This looks like it might do the trick. The question I have however is how would this get run against the excel Work Order Report. The idea here is to get the report ad place this formula into the report at the far right of the report and then drag the formula down to the end of the report. Or maybe once this is figured out maybe the formula can be converted to VBA Code and run against the report. What's your thoughts on this?
JMB17 gave us a great start with his LHS spreadsheet.
I've extended the table and added what I call a "cascading data validation system" in the attached. This does require the most current version of Excel because it makes extensive use of the FILTER and UNIQUE functions.
So Carl_61 there's now a simple formula that identifies which conditions are LHS based on the table at the far left of the sheet.
- Thank you sir, I can say it looks good but I am not clear on what is going on here. The question is how do I apply this against my report? I get a work order report with sometimes up to 300 work orders, give or take, and I need a way to run a solution against the report whereby each work order is checked via the solution we come up with. I'm not apposed to a VBA Code solution as well. What's your thoughts on how I process this solution against the report?
