IF and AND statements

Santosh Varakanti

Hi, I'm using the below formula,

=IF([Process Engineering Approval - CN]1 = "Submitted", "Request Submitted to Process Engineering", IF([Quality Department Approval - CN]1 = "Submitted", "Request Submitted To Quality department", IF([Production Department Approval - CN]1 = "Submitted", "Request Submitted to Production department", IF([Manufacturing Department Approval - CN]1 = "Submitted", "Request Submitted to Manufacturing department", IF([Maintenance Department Approval - CN]1 = "Submitted", "Request Submitted to Maintenance department", IF([H & S Department Approval - CN]1 = "Submitted", "Request Submitted to H&S department", IF(AND([Process Engineering Approval - CN]1 = "Completed", [Quality Department Approval - CN]1 = "Completed", [Production Department Approval - CN]1 = "Completed", [Manufacturing Department Approval - CN]1 = "Completed", [Maintenance Department Approval - CN]1 = "Completed", [H & S Department Approval - CN]1 = "Completed"), "Completed", "Declined")))))))

for onw my approval process. The problem, is when the cell shows "Submitted", it should give me Request submitted to X department". But, this workflow is in progress, the next cell in the workflow, when it is empty, the resultant cell is giving me "Declined".

I'm not sure how use the formula to show "Empty or Blank" in the resultant cell, when the cells are empty. Below is the screenshot of the sheet.

Can anyone please help me with the the correct formula, that can show blank resultant cell, when the cells are blank, "Request Submitted to X department" when cells have "Submitted", "Approved" when all the cells are approved and "Declined" if any of them declines.

David Tutwiler

I think what you're looking for is the ISBLANK() function. You'll need to insert that into your IF(AND()) tree and check for a blank field. It returns a True or False so it would be something like:

=IF(ISBLANK([Process Engineering Approval - CN]@row), "This is blank", "not blank")

Santosh Varakanti

Can I add this to the same formula tree? at start or end?

Santosh Varakanti

=IF(ISBLANK([Process Engineering Approval - CN]1) = "Blank", "NB"), IF(ISBLANK([Quality Department Approval - CN]1) = "Blank", "NB"), IF(ISBLANK([Production Department Approval - CN]1) = "Blank", "NB"), IF(ISBLANK([Manufacturing Department Approval - CN]1) = "Blank", "NB"), IF(ISBLANK([Maintenance Department Approval - CN]1) = "Blank", "NB"), IF(ISBLANK([H & S Department Approval - CN]1) = "Blank", "NB"), IF(AND([Process Engineering Approval - CN]1 = "Completed", [Quality Department Approval - CN]1 = "Completed", [Production Department Approval - CN]1 = "Completed", [Manufacturing Department Approval - CN]1 = "Completed", [Maintenance Department Approval - CN]1 = "Completed", [H & S Department Approval - CN]1 = "Completed"), "Completed", "Declined")

Would this work for my case?