IF Statement needs to ignore blank cell sometimes

Options

Sometimes there are two approvers, sometimes only one. If there is only one and they "Confirm", I record a date column otherwise not and my workflow runs. Sometimes there are two, if they both need to "Confirm" and then I record the date column. If either rejects I don't record the date and run a workflow. The problem I am having is ignoring the second approver when not required.

Tags:

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    What is your existing formula, how is it supposed to function?

  • Charles_M
    Options

    @Paul Newcome I got it to work 90% how I need it too with this formula: IF([COMPLIANCE DATE ACCEPTED TENANT 1]@row = "Confirmed", IF([COMPLIANCE DATE ACCEPTED TENANT 2]@row = "Confirmed", [COMPLIANCE INSPECTION OPENING]@row, IF([COMPLIANCE DATE ACCEPTED TENANT 2]@row = "", [COMPLIANCE INSPECTION OPENING]@row)))


    The only problem I have is that if Compliance Date Accepted Tenant 2 is Confirmed but Compliance Date Accepted Tenant 1 is blank it should not really return the Compliance Inspection Opening as value if true. But it's actually sort of okay because although tenant 2 should not exist if tenant 1 doesn't, it's possible there would be user error with they put the single person in as tenant 2. So the formula would work out in that case. Not sure if you know what I'm saying, sounds confusing.....

  • sharkasits
    sharkasits ✭✭✭✭✭
    edited 07/07/23
    Options

    @Charles_M do you have other fields that would be filled in or blank based on if there are 1 or 2 tenants? If yes, I would capture if there are multiple tenants in a checkbox and then use the following:

    IF([Multiple tenants]=1, IF(AND([COMPLIANCE DATE ACCEPTED TENANT 1]@row = "Confirmed", [COMPLIANCE DATE ACCEPTED TENANT 2]@row = "Confirmed"),  [COMPLIANCE INSPECTION OPENING]@row , ""), IF(OR([COMPLIANCE DATE ACCEPTED TENANT 1]@row = "Confirmed", [COMPLIANCE DATE ACCEPTED TENANT 2]@row = "Confirmed"),  [COMPLIANCE INSPECTION OPENING]@row , ""))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!