Developing existing formula

sahilhq
sahilhq ✭✭✭✭✭✭

Hi,

I have a formula set up to basically check if an action is open. Currently the formula is as follows:

=IF(OR([Post AFC Change Status]7 = "1. Received", [Post AFC Change Status]7 = "7. Withdrawn", [Post AFC Change Status]7 = "2. QA Rejected", [Post AFC Change Status]7 = "6. Returned by NR PM", ISBLANK([Post AFC Change Status]7)), 0, 1). I have also attached a screenshot of the formula from the sheet.

I would like to develop this formula further by adding another criteria, "PE Rejected" column in the sheet is NOT checked. Basically if that box is not checked then the action needs to continue to be shown as open. How do I translate this in terms of adding it to the existing formula?


«1

Answers

  • Try this


    =IF(OR([Post AFC Change Status]7 = "1. Received", [Post AFC Change Status]7 = "7. Withdrawn", [Post AFC Change Status]7 = "2. QA Rejected", [Post AFC Change Status]7 = "6. Returned by NR PM", ISBLANK([Post AFC Change Status]7)), 0, IF([RE Rejected]7 = 0, 0, 1))


    Regards,

    Josh

  • sahilhq
    sahilhq ✭✭✭✭✭✭

    @Josh Strong Hi! I tried the formula but it didn't work. This is what I tried:

    =IF(OR([Post AFC Change Status]8 = "1. Received", [Post AFC Change Status]8 = "7. Withdrawn", [Post AFC Change Status]8 = "2. QA Rejected", [Post AFC Change Status]8 = "6. Returned by NR PM", ISBLANK([Post AFC Change Status]8)), 0, IF([PE Rejected]8=, 0, 0, 1))

    I got the -#unparseabble error message. See attached screenshot of the formula I used.


  • =IF(OR([Post AFC Change Status]8 = "1. Received", [Post AFC Change Status]8 = "7. Withdrawn", [Post AFC Change Status]8 = "2. QA Rejected", [Post AFC Change Status]8 = "6. Returned by NR PM", ISBLANK([Post AFC Change Status]8)), 0, IF([PE Rejected]8= 0, 0, 1))

    Sorry try this one, i had a random common in the formula

    Josh

  • sahilhq
    sahilhq ✭✭✭✭✭✭

    @Josh Strong Still not quite right. The formula isn't giving me an error message anymore but the box is still checked. I want it to be unchecked if any of the conditions in the formula is met - including the function we have added in the formula.

  • sahilhq
    sahilhq ✭✭✭✭✭✭

    @Josh Strong Hi Josh. A quick update - I managed to achieve what I wanted. I just had to remove a 0 in the end of the formula you had shared with me for the "open" column to be unchecked. I have one final question to ask. There is another formula, that I would like to update and would really appreciate your help. Can we add the same function where if "PE Rejected" column in the sheet is not checked to the long and lengthy formula shown below?


    =IF(ISBLANK([Supplier Post AFC Change No.]10), 0, IF(OR([Post AFC Change Status]10 = "7. Withdrawn", [Post AFC Change Status]10 = "2. QA Rejected", [Post AFC Change Status]10 = "6. Returned by NR PM", ISBLANK([Response Due Date (10 working days)]10)), 0, IF([LDO QA Check Complete]10 = "TBC", 0, IF([Response Due Date (10 working days)]10 < TODAY(), 1, 0))))



  • sahilhq
    sahilhq ✭✭✭✭✭✭

    @Paul Newcome Hi Paul! Can you kindly help me with the query above?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Where would you want it added? What would you want the output to be if it is not checked?

  • =IF(ISBLANK([Supplier Post AFC Change No.]10), 0, IF(OR([Post AFC Change Status]10 = "7. Withdrawn", [Post AFC Change Status]10 = "2. QA Rejected", [Post AFC Change Status]10 = "6. Returned by NR PM", ISBLANK([Response Due Date (10 working days)]10)), 0, IF([LDO QA Check Complete]10 = "TBC", 0, IF([Response Due Date (10 working days)]10 < TODAY(), 1, 0))), IF([PE Rejected]8= 0, 1, 0))


    Try this one.


    Josh

  • sahilhq
    sahilhq ✭✭✭✭✭✭

    @Josh Strong Hi! The formula provided doesn't work - I get an incorrect argument message. See attached screenshot of the formula that didn't work....


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @sahilhq It looks like you may have a misplaced parenthesis. Take one of the closing parenthesis from before the final IF statement and move it to the end.


    =IF(ISBLANK([Supplier Post.....................................................................................................................................))), IF([PE Rejected]24 = 0, 1, 0))


    changes to


    =IF(ISBLANK([Supplier Post.....................................................................................................................................)), IF([PE Rejected]24 = 0, 1, 0)))

  • sahilhq
    sahilhq ✭✭✭✭✭✭

    @Paul Newcome Still doesn't work....see attached formula I inserted. I am getting the incorrect argument again.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Can you copy/paste the entire formula here directly from the sheet?

  • sahilhq
    sahilhq ✭✭✭✭✭✭

    @Paul Newcome =IF(ISBLANK([Supplier Post AFC Change No.]6), 0, IF(OR([Post AFC Change Status]6 = "7. Withdrawn", [Post AFC Change Status]6 = "2. QA Rejected", [Post AFC Change Status]6 = "6. Returned by NR PM", ISBLANK([Response Due Date (10 working days)]6)), 0, IF([LDO QA Check Complete]6 = "TBC", 0, IF([Response Due Date (10 working days)]6 < TODAY(), 1, 0)), IF([PE Rejected]8= 0, 1, 0)))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    =IF(ISBLANK([Supplier Post AFC Change No.]6), 0, IF(OR([Post AFC Change Status]6 = "7. Withdrawn", [Post AFC Change Status]6 = "2. QA Rejected", [Post AFC Change Status]6 = "6. Returned by NR PM", ISBLANK([Response Due Date (10 working days)]6), 0, IF([LDO QA Check Complete]6 = "TBC", 0, IF([Response Due Date (10 working days)]6 < TODAY(), 1, IF([PE Rejected]8= 0, 1, 0)))))


    If that does not work, can you provide the logic behind each piece? We can try a rewrite if needed.

  • sahilhq
    sahilhq ✭✭✭✭✭✭

    @Paul Newcome

    Sorry for the delay in response - the formula provided still doesn't work.

    Logic behind each piece:

    • IF(ISBLANK([Supplier Post AFC Change No.]6), 0 - This part of the formula is just saying that if the unique id isn't blank then check.

    • IF(OR([Post AFC Change Status]6 = "7. Withdrawn", [Post AFC Change Status]6 = "2. QA Rejected", [Post AFC Change Status]6 = "6. Returned by NR PM" - There is a column in the sheet that is displaying the current status of the post afc change. All the drop downs in this column are - 1. Received, 2. QA Rejected, 3. Issued to PE, 4. Answered by PE, 5. Approved by DPE, 6. Returned by NR PM, 7. Withdrawn. So what this formula is trying to do is that if the status is NOT one of withdrawn, QA rejected, Returned by NR PM (which is when it's closed), i.e. open then check.

    • ISBLANK([Response Due Date (10 working days)]6), 0, - Since this formula is an overdue column, this part of the formula is trying to pick up if the response due date is greater than 10 days then check. Basically if it is greater than 10 days (i.e. overdue) then check.

    • IF([LDO QA Check Complete]6 = "TBC", 0, - There is a seperate column for a check at the start of the process so if there is a date in the cell in this column then check.

    • IF([Response Due Date (10 working days)]6 < TODAY(), 1, - There is another column that counts 14 days + LDO QA check complete.

    • IF([PE Rejected]8= 0, 1, 0))))) - This is the part that we have added to the formula because we added another column in the sheet that tries to check if someone checks that box then don't count. If not then do count if that makes sense?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!