Developing existing formula

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?

Formula.PNG

Josh Strong

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

@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.

Capture.PNG

Josh Strong

=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

@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

@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))))

Capture1.PNG

sahilhq

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

Paul Newcome

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

Josh Strong

=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

@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....

pac.PNG

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

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

INCORRECT ARGUEMENT.PNG

Paul Newcome

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

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

=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

@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?