formula

Susan van Niekerk

Hi

Trying to get a formula to report when there are tasks "Missing"

so when below columns are blank or the Two are not "APPROVED" I want to return TASK OUTSTANDING and when all not blank and the two APPROVED return COMPLETED

I have tried several way blank and not blank but cant get it to return what I want when I want :-)

=IF(OR(ISBLANK([Helper 1]@row), ISBLANK([Native Title Group]@row), ISBLANK([Traditional Owner]@row), ISBLANK(Consultant@row), ISBLANK([BSR number]@row), ISBLANK([Study Area URL Link]@row), ISBLANK([LAP Number]@row), ISBLANK([CMS Number]@row), ISBLANK([CMS complete]@row), ISBLANK([PO number]@row), ISBLANK([Supervisor Assigned]@row), ISBLANK([MESO Assigned]@row), [REG Status]@row <> "APPROVED", [LAP status]@row <> "APPROVED"), "TASK OUTSTANDING", "COMPLETED")

Protonsponge

Hello @Susan van Niekerk,

Would the following formula give you what you are looking for?

=IF(OR(ISBLANK([Helper 1]@row), ISBLANK([Native Title Group]@row), ISBLANK([Traditional Owner]@row), ISBLANK(Consultant@row), ISBLANK([BSR number]@row), ISBLANK([Study Area URL Link]@row), ISBLANK([LAP Number]@row), ISBLANK([CMS Number]@row), ISBLANK([CMS Complete]@row), ISBLANK([PO Number]@row), ISBLANK([Supervisor Assigned]@row), ISBLANK([MESO Assigned]@row)), "TASK OUTSTANDING", IF(OR([REG Status]@row <> "APPROVED", [LAP status]@row <> "APPROVED"), "TASK OUTSTANDING", "COMPLETE"))

I hope that is helpful to you in some way,

Protonsponge

Susan van Niekerk

Hi

It is returning TASK OUTSTANDING even when all the fields are completed and my two approval columns are Approved

Protonsponge

Hello @Susan van Niekerk,

How is your [Helper 1] column populated and is it possible that cell is blank in your test row?

I don't believe the column types should impact the formula and I can see that you have entries in all the relevant columns and so wonder if your helper column may be blank?

Protonsponge

Paul Newcome

What does this do for you?

=IF(OR(ISBLANK([Helper 1]@row), ISBLANK([Native Title Group]@row), ISBLANK([Traditional Owner]@row), ISBLANK(Consultant@row), ISBLANK([BSR number]@row), ISBLANK([Study Area URL Link]@row), ISBLANK([LAP Number]@row), ISBLANK([CMS Number]@row), ISBLANK([CMS complete]@row), ISBLANK([PO number]@row), ISBLANK([Supervisor Assigned]@row), ISBLANK([MESO Assigned]@row), AND([REG Status]@row <> "APPROVED", [LAP status]@row <> "APPROVED")), "TASK OUTSTANDING", "COMPLETED")

Susan van Niekerk

Hi

@Protonsponge and @Paul Newcome

The issue was the Helper column because it is only Not blank when it is a parent row - so have switched to task Helper column and using the AND in the formula it worked

Thank you so much