Forum Discussion

marcellojek's avatar
marcellojek
Copper Contributor
Nov 07, 2022

Return value is specific arguments are true

Hi all,

 

I am working on a construction site, where we have to fill out and commit some checklists before we cast the foundations with concrete. I have an Excel sheet with the list of foundations and a list of checklists - in one checklist we can commit several foundations for inspection. 

This is what the foundation list checklist looks like: 

 

And this is what the checklist sheet looks like:

 

There are checklists before we cast the concrete (Pre pour) and after the concrete is cast (Post pour). Now, I want to automate the process, so that Excel automatically ticks off the foundation once the checklist is marked as Finished.  Let's take the foundation P35-C5 as an example. In the Foundation list sheet, I would like Excel to return an "OK" value if the following conditions in the Checklists sheet are fulfilled:
- "x" in Pre pour? column 

- Structure column contains the name of the foundation (P35-C5)

- Status is Finished

 

In other words, I would like Excel to check whether there is a Finished pre pour checklist containing the given foundation and tick it off in the Foundation list. 

I tried with AND and OR functions, but these return the positive value only if all or any of the arguments are true, and therefore do not work in this case:

 

 

 

=IF(OR(ISNUMBER(SEARCH("x";Checklists!$D$4:$D$406));ISNUMBER(SEARCH("P35-C5";Checklists!$H$4:$H$406));ISNUMBER(SEARCH("Finished";Checklists!$J$4:$J$406)));"OK";"")

 

 


Can this be resolved with functions, or macros, or am I asking for too much?

excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Nov 07, 2022

    marcellojek

    Let's say that P35-C5 is in A10 on the Foundations sheet.

     

    =IFERROR(IF(AND(INDEX(Checklists!$D$4:$D$406;MATCH("*"&A10&"*";CheckLists!$H$4:$H$406;0))="x";INDEX(Checklists!$H$4:$H$406;MATCH("*"&A10&"*";CheckLists!$H$4:$H$406;0))="Finished");"OK";"");"")

     

    This could return an incorrect result if there are foundations named for example P35-C50 or P35-C58. To avoid that, use

     

    =IFERROR(IF(AND(INDEX(Checklists!$D$4:$D$406;MATCH("*"&A10&CHAR(10)&"*";CheckLists!$H$4:$H$406&CHAR(10);0))="x";INDEX(Checklists!$H$4:$H$406;MATCH("*"&A10&"*";CheckLists!$H$4:$H$406;0))="Finished");"OK";"");"")

     

    You may have to confirm the formula by pressing Ctrl+Shift+Enter.

Resources