Help with long ugly nested IF formula pleeeease :D

Hi everyone! Super new to Airtable (as of today actually) and I’m trying to have a “Status” column update based off a horrendous nested IF statement referencing multiple other columns, shown here:

  • MD Signoff (Not Started, In Process, Pending adjustment, Complete)
  • RN Signoff (Not Started, In Process, Pending adjustment, Complete)
  • Rx Signoff (Not Started, In Process, Pending adjustment, Complete)

I’m using the following formula (please don’t make fun of me :frowning: lol) but am only getting some of it to work as expected:
IF(
AND(
{MD Signoff}=“Complete”, {RN Signoff} = “Complete”, {Rx Signoff} = “Complete”
), “Complete”,
IF(
AND(
{MD Signoff} = “Complete”, {RN Signoff} = “Complete”, {Rx Signoff} != “Complete”
),
IF(
AND(
{MD Signoff} = “Complete”, {RN Signoff} != “Complete”, {Rx Signoff} =“Complete”
),
IF(
AND(
{MD Signoff} = “Complete”, {RN Signoff} != “Complete”, {Rx Signoff} != “Complete”
),
IF(
AND(
{MD Signoff} != “Complete”, {RN Signoff} = “Complete”, {Rx Signoff} = “Complete”
),
IF(
AND(
{MD Signoff} != “Complete”, {RN Signoff} = “Complete”, {Rx Signoff} != “Complete”
),
IF(
AND(
{MD Signoff} != “Complete”, {RN Signoff} != “Complete”, {Rx Signoff} = “Complete”
),
IF(
AND(
{MD Signoff} != “Complete”, {RN Signoff} != “Complete”, {Rx Signoff} != “Complete”
),
“Pending Rx Review”
),
“Pending RN Review”
),
“Pending RN and Rx Review”
),
“Pending MD Review”
),
“Pending MD and Rx Review”
),
“Pending MD and RN Review”
),
“Not Started”
)
)

Am I better off using a switch? I’m not surprised to see that the above formula isn’t working but need your help figuring out a better approach pleeeeeeeeease! Thank you all in advance!

  • Kareem

Hi @Kareem_Abdalla,

Welcome to Airtable ! :smiley:

Well, the nested formula you copied does not make much senes, you have added the “complete” argument for all the items several times.

You cannot use a Switch formula because you have many variables.

Here is a working Formula

IF(AND({MD Signoff}="Complete",{RN Signoff} = "Complete", {Rx Signoff} = "Complete"), "Complete",IF(AND({MD Signoff}="Complete",{RN Signoff} = "Complete", {Rx Signoff} != "Complete"), "Pending Rx Review",IF(AND({MD Signoff}="Complete",{RN Signoff} != "Complete", {Rx Signoff} = "Complete"), "Pending RN Review",IF(AND({MD Signoff}="Complete",{RN Signoff} != "Complete", {Rx Signoff} != "Complete"), "Pending RN and Rx Review", IF(AND({MD Signoff}!="Complete",{RN Signoff} = "Complete", {Rx Signoff} != "Complete"), "Pending MD and Rx Review", IF(AND({MD Signoff}!="Complete",{RN Signoff} != "Complete", {Rx Signoff} != "Complete"), "Not Started",IF(AND({MD Signoff}!="Complete",{RN Signoff} != "Complete", {Rx Signoff} = "Complete"), "Pending MD and RD Review", IF(AND({MD Signoff}!="Complete",{RN Signoff} = "Complete", {Rx Signoff} = "Complete"), "Pending MD Review"))))))))

BR,
Mo

@Mohamed_Swellam Thank you so much!!! Yeah I messed that up pretty bad lol I really appreciate the help my friend!

JAK,
K

1 Like