Executing a formula with IF statement

Options

I'm trying to adjust my formula to only run when certain criteria within the sheet is met.

What I'd like to achieve is a formula where IF approval status IS Approved, assign a P-Code.

The P-Code formula is as follows:

=LEFT(Department@row, 3) + "-" + (YEAR([Created date]@row) + "-" + IF(Prefix@row <> "", Prefix@row, "") + Suffix@row + "")

I don't want to assign a P-Code if the project is pending or declined.

What would I need to add to run the formula only when "Approved" is selected?


Best Answer

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 05/30/24 Answer ✓
    Options

    Absolutely. Just change it from

    =IF([Approval Status]@row<>"Approved", "", COUNTIFS(AutoNum:AutoNum, >=AutoNum@row, [Approval Status]:[Approval Status],="Approved", Department:Department, =Department@row))

    To

    =IF([Approval Status]@row<>"Approved", "", COUNTIFS(AutoNum:AutoNum, <=AutoNum@row, [Approval Status]:[Approval Status],="Approved", Department:Department, =Department@row))

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Answers

  • bisaacs
    bisaacs ✭✭✭✭✭
    Options

    Hey @BrendonP12222,

    You should be able to use an IF statement for that:

    =IF([Approval Status]@row = "Approved", LEFT(Department@row, 3) + "-" + (YEAR([Created date]@row) + "-" + IF(Prefix@row <> "", Prefix@row, "") + Suffix@row + ""), "")

    Hope this helps!

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    @BrendonP12222

    Try This out and see how it works for you.

    =IF([Approval Status]@row = "Approved", LEFT(Department@row, 3) + "-" + (YEAR([Created date]@row) + "-" + IF(Prefix@row <> "", Prefix@row, "") + Suffix@row + ""))

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • BrendonP12222
    Options

    Thanks! I did a poor job of explaining because what I also hope to accomplish is to not "count" the Declined projects.

    For example, I'd like to see the P-Code for Row 3 read as "Acc-2024-2" since Row 2 isn't being assigned a P-Code.

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 05/30/24
    Options

    So based on what I'm understanding you have a Suffix row that is an Autonumber? IF so I would recommend an Auto number that actually only looks if something is Approved. Which is done through a formula instead of a system generated auto number. While still using the auto number as a helper for your new Suffix column.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • BrendonP12222
    BrendonP12222 ✭✭
    edited 05/30/24
    Options

    Here's what I have…

    Prefix: =PARENT() + IF([Parent ID]@row = "TOP", "", PARENT(Suffix@row) + ".")
    Suffix: =IF([Approval Status]@row = "Approved", MATCH(UniqueID@row, COLLECT(UniqueID:UniqueID, [Parent ID]:[Parent ID], [Parent ID]@row, Department:Department, Department@row), 0))
    Unique ID: =IFERROR(Name@row + AutoNum@row, "")

    I'm still hoping to have the Row 3 suffix show up as "2" because no suffix was assigned for Row 2 since the project was marked as "declined"

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 05/30/24
    Options

    Try something along this line for your Suffix column

    =IF([Approval Status]@row= "Approved", COUNTIFS(AutoNum:AutoNum, >=AutoNum@row, [Approval Status]:[Approval Status], =[Approval Status]@row))

    What this does is a Sequential Count IF status is approved. That way the suffix will only be the next number in the sequence.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    ok give me a moment. I was editing my comment when you posted.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 05/30/24
    Options

    Use this formula to generate the Suffix number. This Creates A Sequential number based on if a status is approved For Each Department. So it should have a different sequence depending on the department. "Starting over at 1 for each department and increasing by one for each department"

    =IF([Approval Status]@row<>"Approved", "", COUNTIFS(AutoNum:AutoNum, >=AutoNum@row, [Approval Status]:[Approval Status],="Approved", Department:Department, =Department@row))

    Then run this if statement for the P-code. That should fix your issue.

    =IF([Approval Status]@row = "Approved", LEFT(Department@row, 3) + "-" + (YEAR([Created date]@row) + "-" + IF(Prefix@row <> "", Prefix@row, "") + Suffix@row + ""))

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • BrendonP12222
    Options

    First three letters of the "Department" cell

    =LEFT(Department@row, 3) + "-" + (YEAR([Created date]@row) + "-" + IF(Prefix@row <> "", Prefix@row, "") + Suffix@row + "")

    So ideally, the P-Codes could look something like this
    ACC-2024-1
    PRO-2024-1
    ACC-2024-2
    ACC-2024-3
    PRO-2024-2

    And only assigning a code if the project was approved.

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    That's what I was trying to explain in my above post. Lol

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • BrendonP12222
    Options

    Almost there!! Is there a way to reverse the COUNTIF now?

    When I input a new project, I want the latest project to be the highest/last number in the sequence.
    In the attached screenshot, I'd like row 5 to be Acc-2024-3 instead of Acc-2024-1

    And then when I input a new project into Row 6 (assuming it's Accounting & Finance) it's P-Code would be Acc-2024-4

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 05/30/24 Answer ✓
    Options

    Absolutely. Just change it from

    =IF([Approval Status]@row<>"Approved", "", COUNTIFS(AutoNum:AutoNum, >=AutoNum@row, [Approval Status]:[Approval Status],="Approved", Department:Department, =Department@row))

    To

    =IF([Approval Status]@row<>"Approved", "", COUNTIFS(AutoNum:AutoNum, <=AutoNum@row, [Approval Status]:[Approval Status],="Approved", Department:Department, =Department@row))

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • BrendonP12222
    Options

    I owe you a drink sir!

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    Your welcome. I am glad I could help out. We have all been there.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!