How do i get a cell to return a TEXT value based on several IF criteria from other cells?

paul112233

Hi

I have an audit planner which i am trying to automate some what, i would like the status to change based on a set criteria.

I.e. AUDIT STATUS = "Awaiting Scheduling" IF IMS AUDIT is CHECKED and SCHEDULED DATE is BLANK

AUDIT STATUS = "Scheduled" IF IMS AUDIT is CHECKED and SCHEDULED DATE is NOT BLANK and COMPPETED DATE is BLANK

AUDIT STATUS = "Overdue' IF IMS AUDIT is CHECKED and SCHEDULED DATE is NOT BLANK and COMPETED DATE is BLANK and PROPOSED AUDIT DATE is less than TODAY.

AUDIT STATUS = "Completed" IF IMS AUDIT is CHECKED and SCHEDULED DATE is NOT BLANK and COMPLETED DATE is NOT BLANK

Any help would be greatly appreciated

Brett Wyrick

This ought to do it:

=IF(AND([IMS audit]@row = true, [IMS Audit Scheduled Date]@row = ""), "Awaiting Scheduling", IF(AND([IMS audit]@row = true, [IMS Audit Scheduled Date]@row <> "", [IMS Audit Completed Date]@row = "", [IMS Audit Proposed Audit Month]@row < TODAY()), "Overdue", IF(AND([IMS audit]@row = true, [IMS Audit Scheduled Date]@row <> "", [IMS Audit Completed Date]@row = ""), "Scheduled", IF(AND([IMS audit]@row = true, [IMS Audit Scheduled Date]@row <> "", [IMS Audit Completed Date]@row <> ""), "Completed", ""))))

Parker Oxford

See formula below, it should work if you copy and paste into your column so let me know if anything doesn't work.

If there are any issues, ensure I got all the column names correct.

=IF(AND([IMS Audit]@row="TRUE",[IMS Audit Scheduled Date]@row=""),"Awaiting Scheduling",IF(AND([IMS Audit]@row="TRUE",[IMS Audit Scheduled Date]@row<>"",[IMS Audit Completed Date]@row=""),"Scheduled",IF(AND([IMS Audit]@row="TRUE",[IMS Audit Scheduled Date]@row<>"",[IMS Audit Completed Date]@row="",[IMS Audit Proposed Audit Month]@row<TODAY()),"Overdue",IF(AND([IMS Audit]@row="TRUE",[IMS Audit Scheduled Date]@row<>"",[IMS Audit Completed Date]@row<>"", "Completed",""))))

Brett Wyrick

This ought to do it:

=IF(AND([IMS audit]@row = true, [IMS Audit Scheduled Date]@row = ""), "Awaiting Scheduling", IF(AND([IMS audit]@row = true, [IMS Audit Scheduled Date]@row <> "", [IMS Audit Completed Date]@row = "", [IMS Audit Proposed Audit Month]@row < TODAY()), "Overdue", IF(AND([IMS audit]@row = true, [IMS Audit Scheduled Date]@row <> "", [IMS Audit Completed Date]@row = ""), "Scheduled", IF(AND([IMS audit]@row = true, [IMS Audit Scheduled Date]@row <> "", [IMS Audit Completed Date]@row <> ""), "Completed", ""))))

Parker Oxford

See formula below, it should work if you copy and paste into your column so let me know if anything doesn't work.

If there are any issues, ensure I got all the column names correct.

=IF(AND([IMS Audit]@row="TRUE",[IMS Audit Scheduled Date]@row=""),"Awaiting Scheduling",IF(AND([IMS Audit]@row="TRUE",[IMS Audit Scheduled Date]@row<>"",[IMS Audit Completed Date]@row=""),"Scheduled",IF(AND([IMS Audit]@row="TRUE",[IMS Audit Scheduled Date]@row<>"",[IMS Audit Completed Date]@row="",[IMS Audit Proposed Audit Month]@row<TODAY()),"Overdue",IF(AND([IMS Audit]@row="TRUE",[IMS Audit Scheduled Date]@row<>"",[IMS Audit Completed Date]@row<>"", "Completed",""))))

paul112233

Great, many thanks guys, most appreciated.😀