Options
✭✭
edited 12/09/19

Hi, I am hoping someone can help with the below formula additions we would like to make as it would be greatly appreicated!

• Today our formula provides a status of In Progress for NIR code and LI code in the SMCC Action column. Once an actual date is entered into the “Actual Date Request Completed” column it will flip the Status column to completed. If there is a code NEM the Status column will read No Action – NEM.
• What we would like to do is add a few more codes of BDM and MIN in dropdown of the SMCC Action column and also have them read No Action like the NEM code in Status column.

Our current formula is listed below and works great but we just need to add the additional BDM and MIN codes. If anyone could please help to complete!

=IF(AND(ISBLANK([Actual Date Request Completed]@row), [SMCC Action]@row = "NEM"), [SMCC Action]@row= "BDM"), "No Action", IF(ISBLANK([Actual Date Request Completed]@row), "In

Progress", "Completed"
))

• ✭✭✭✭✭✭
Options

The syntax in your above formula is incorrect and should not be working. Can you double check to make sure the actual formula used is posted? It would be much easier to build on that way.

• ✭✭
Options

hi Paul, Thank you! Please see below...

=IF(AND(ISBLANK([Actual Date Request Completed]@row), [SMCC Action]@row = "NEM"), "No Action - NEM", IF(ISBLANK([Actual Date Request Completed]@row), "In Progress", "Completed"))

• ✭✭✭✭✭✭
Options

Ok. That definitely flows better than the one in your original post. Haha.

All you really need to do is continue the pattern of the nested IF's.

=IF(AND(ISBLANK([Actual Date Request Completed]@row), [SMCC Action]@row = "NEM"), "No Action - NEM", IF(AND(ISBLANK([Actual Date Request Completed]@row), [SMCC Action]@row = "BDM"), "No Action - BDM", IF(AND(ISBLANK([Actual Date Request Completed]@row), [SMCC Action]@row = "MIN"), "No Action - MIN", IF(ISBLANK([Actual Date Request Completed]@row), "In Progress", "Completed"))))

• ✭✭
Options

Perfect! Thank you Paul...Greatly appreciated!

• ✭✭✭✭✭✭
Options