Status Formula

Canteaug

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?

heyjay

Are you entering the formula in the column which is one of your reference?

https://help.smartsheet.com/articles/2476176-formula-error-messages#toc--circular-reference

Carson Penticuff

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

Canteaug

@Carson Penticuff @heyjay the status column is the column in which I am referencing.

heyjay

The last arguement is referencing to the column itself Status@row

Canteaug

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

heyjay

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