Nested IF Statement

RRIOS
RRIOS ✭✭
edited 03/21/22 in Smartsheet Basics

Can someone help me fix this formula:

=IF([Provider_Helper]@row = "Provider", JOIN(UPPER([FName_Helper]@row + " " + [Last Name]@row) + ", " + Credential@row), ([Provider_Helper]@row = "Resident", JOIN(UPPER([FName_Helper]@row + " " + [Last Name]@row) + ", " + Credential@row) + " (" + Role@row) + ")", (JOIN(UPPER([First Name]@row + " " + [LName_Helper]@row) + ", " + Credential@row))

I keep getting an "unparsable" error.

I'm trying to satisfy the following:

  1. If Provider_Helper = "Provider" then JOIN(UPPER([FName_Helper]@row + " " + [Last Name]@row) + ", " + Credential@row)
  2. If Provider_Helper = "Resident" then JOIN(UPPER([FName_Helper]@row + " " + [Last Name]@row) + ", " + Credential@row) + " (" + Role@row) + ")"
  3. If Provider_Helper is not "Provider" or "Resident" then (JOIN(UPPER([First Name]@row + " " + [LName_Helper]@row) + ", " + Credential@row)

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    My apologies. I missed the full name vs initial bit in the first formula. The formula I provided will output:

    1. If "Provider" = J. Doe, MD
    2. If "Resident" = J. Doe, MD (R1)
    3. If not "Provider" or "Resident" = J. Doe, RN


    Now that I see you want to switch it up a little bit on that last piece, we want to use this instead:

    =IF(OR([Provider_Helper]@row = "Provider", [Provider_Helper]@row = "Resident"), [FName_Helper]@row + " " + [Last Name]@row + ", " + Credential@row + IF([Provider_Helper]@row = "Resident", " (" + Role@row + ")"), [First Name]@row + " " + [LName_Helper]@row + ", " + Credential@row)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try this:


    =UPPER([FName_Helper]@row) + " " + UPPER([Last Name]@row) + ", " + UPPER(Credential@row) + IF([Provider_Helper]@row = "Resident", " (" + UPPER(Role@row) + ")")

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • RRIOS
    RRIOS ✭✭
    edited 03/21/22

    @Paul Newcome, sorry man, I think I might have got lost in the abbreviated formula.

    Can you send what the entire string should reflect?

    The result should reflect the following:

    1. Argument 1 (if "Provider") = J. Doe, MD
    2. Argument 2 (if "Resident") = J. Doe, MD (R1)
    3. Argument 3 (if not "Provider" or "Resident") = John D., RN
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    My apologies. I missed the full name vs initial bit in the first formula. The formula I provided will output:

    1. If "Provider" = J. Doe, MD
    2. If "Resident" = J. Doe, MD (R1)
    3. If not "Provider" or "Resident" = J. Doe, RN


    Now that I see you want to switch it up a little bit on that last piece, we want to use this instead:

    =IF(OR([Provider_Helper]@row = "Provider", [Provider_Helper]@row = "Resident"), [FName_Helper]@row + " " + [Last Name]@row + ", " + Credential@row + IF([Provider_Helper]@row = "Resident", " (" + Role@row + ")"), [First Name]@row + " " + [LName_Helper]@row + ", " + Credential@row)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • RRIOS
    RRIOS ✭✭

    This works, thank you so much!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com