Executing a formula with IF statement

BrendonP12222

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?

Mark.poole

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))

bisaacs

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!

Mark.poole

@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 + ""))

BrendonP12222

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

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.

BrendonP12222

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

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.

Mark.poole

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

Mark.poole

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 + ""))

BrendonP12222

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

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

BrendonP12222

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

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))

BrendonP12222

I owe you a drink sir!

Mark.poole

https://community.smartsheet.com/discussion/comment/438376#Comment_438376

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