Status Formula
I keep getting an error stating circular reference and I don't understand why.
Here is what I am trying to accomplish:
If Employee Separation Date is in the future change [Status] “HOLD Pending Separation Date"
If the Employee Separation Date is NOT in the future AND “All HR Tasks” is NOT CHECKED change, “PERS Packet Received from Employee” is NOT BLANK and “Completed Packet Sent to PERS” is Blank change [Status] to “Pending HR Action”
If “All HR Tasks Complete” AND “All Support Tasks Complete” change [Status] to “Complete"
Here is my formula: =IF([Employee Separation Date]@row > TODAY(), "HOLD Pending Separation Date", IF(AND([All HR Tasks Complete]@row = 0, [PERS Packet Received from Employee]@row <> "", [Completed Packet Sent to PERS]@row = ""), "Pending HR Action", IF(AND([All HR Tasks Complete]@row = 1, [All Support Tasks Complete]@row = 1), "Complete", Status@row)))
Can someone please help?
Answers
-
Are you entering the formula in the column which is one of your reference?
...
-
Is there a formula in one of the columns you are referencing that includes the column you are placing the formula into? The syntax seems okay...
-
@Carson Penticuff @heyjay the status column is the column in which I am referencing.
-
The last arguement is referencing to the column itself
Status@row
...
-
@heyjay Oh my! Thank you. I took out that part and it worked for two of the status changes.
The only status change that is not working now is (If the Employee Separation Date is NOT in the future AND “All HR Tasks” is NOT CHECKED change, “PERS Packet Received from Employee” is NOT BLANK and “Completed Packet Sent to PERS” is Blank change [Status] to “Pending HR Action”)
-
=IF( [Employee Separation Date]@row > TODAY(), "HOLD Pending Separation Date", IF(AND( [All HR Tasks Complete]@row = 0, [PERS Packet Received from Employee]@row = 0, [Completed Packet Sent to PERS]@row = ""), "Pending HR Action", IF(AND( [All HR Tasks Complete]@row = 1, [All Support Tasks Complete]@row = 1)
Assuming
[PERS Packet Received from Employee]
is a Checkbox column:Change
[PERS Packet Received from Employee]@row <> ""
to[PERS Packet Received from Employee]@row = 0
You last IF Statement doesnt have a TRUE answer. Should that be "Complete?"
IF(AND( [All HR Tasks Complete]@row = 1, [All Support Tasks Complete]@row = 1), "Complete", "Otherwise show this")
...
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 430 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!