Executing a formula with IF statement
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
-
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
-
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!
-
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.
-
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.
-
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.
-
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" -
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.
-
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.
-
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.
-
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-2And only assigning a code if the project was approved.
-
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.
-
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-1And then when I input a new project into Row 6 (assuming it's Accounting & Finance) it's P-Code would be Acc-2024-4
-
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.
-
I owe you a drink sir!
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
