Nested IF formula with input from 2 different columns possible?

I am stuck on a nested if formula. I have 2 columns that set 1 of 3 statuses in a 4th column, here is the basic break down if "APPR'VD BY ARCH'T" is blank, display Awarded in "Project Ordering Status" column, if "APPR'VD BY ARCH'T" is a date, display ABA in "Project Ordering Status" column. Here is the formula that I have in "Project Ordering Status" column-

=IF(ISDATE([APPR'VD BY ARCH'T]@row), "ABA", IF(ISBLANK([APPR'VD BY ARCH'T]@row), "AWARDED", ""))

the part where I get stuck is, I want to add IF(ISDATE) for a separate column, "FAB RLS DATE" is a date, display RFF in Project Ordering Status column.

the linear process goes Awarded, ABA, then RFF. If there is a date in "FAB RLS DATE" column = RFF Project Ordering column

Please help


Best Answer

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓

    I am having some difficulty following exactly what you want. This is what I think you mean, but please correct me if I am wrong.

    IF(ISDATE([FAB RLS DATE]@row), "RFF", IF([APPR'VD BY ARCH'T]@row = "", "Awarded", IF(ISDATE([APPR'VD BY ARCH'T]@row), "ABA")))


    This will set [PROJECT ORDERING STATUS] equal to the following values, with this order of priority:

    1. "RFF" if there is a date in the [FAB RLS DATE] column, regardless of the contents of [APPR'VD BY ARCH'T].
    2. "Awarded" if [APPR'VD BY ARCH'T] is blank and [FAB RLS DATE] does not contain a date.
    3. "ABA" if [APPR'VD BY ARCH'T] contains a date and [FAB RLS DATE] does not contain a date.


Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓

    I am having some difficulty following exactly what you want. This is what I think you mean, but please correct me if I am wrong.

    IF(ISDATE([FAB RLS DATE]@row), "RFF", IF([APPR'VD BY ARCH'T]@row = "", "Awarded", IF(ISDATE([APPR'VD BY ARCH'T]@row), "ABA")))


    This will set [PROJECT ORDERING STATUS] equal to the following values, with this order of priority:

    1. "RFF" if there is a date in the [FAB RLS DATE] column, regardless of the contents of [APPR'VD BY ARCH'T].
    2. "Awarded" if [APPR'VD BY ARCH'T] is blank and [FAB RLS DATE] does not contain a date.
    3. "ABA" if [APPR'VD BY ARCH'T] contains a date and [FAB RLS DATE] does not contain a date.


  • Pnda
    Pnda ✭✭✭

    Hi Carson,

    Yes, that is correct and the formula worked.

    Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!