Circular reference error help

Casie

=IF([Cancel Request]@row1 = "Canceled", [AW Request Status]@row)

=IF([Cancel Request]@row = 1, "Canceled", [AW Request Status]@row)

If cancel request column is check then change AW Request status drop down to "Canceled"

I keep getting error message of #circular reference

Bassam Khalil

Hi @Casie,

The "#circular reference" error in Smartsheet occurs when a formula refers back to itself either directly or indirectly, causing an infinite loop. Based on the formulas you've provided and the intended functionality, it seems that there might be a direct or indirect reference within the same row that causes the sheet to enter an infinite calculation loop. Here's how you can address the issue:

1. Avoid Self-Referencing: Ensure that your formula does not directly or indirectly refer back to the cell or column it’s trying to update. For instance, [AW Request Status]@row should not be manipulated by a formula within the same [AW Request Status] column if it directly depends on its own value.
2. Use Checkbox Properly: From your description, it seems like [Cancel Request]@row might be a checkbox. If so, you should use a formula that checks whether the checkbox is checked (usually 1 for checked and 0 for unchecked) rather than comparing it to "Canceled".

Here is a suggested approach to resolve your issue:

• Assumption: [Cancel Request]@row is a checkbox, and [AW Request Status]@row should be set to "Canceled" if [Cancel Request]@row is checked.

Correct Formula

You should use the following formula in the [AW Request Status] column to automatically set its value to "Canceled" when the [Cancel Request] checkbox is checked, and otherwise leave it as it is (assuming there is another mechanism or manual entry that sets its value):

=IF([Cancel Request]@row = 1, "Canceled", [AW Request Status]@row)

However, this can still cause a circular reference if [AW Request Status]@row is also trying to refer to its own value in the formula. Here’s how you can solve this:

Proposed Solution

• Option 1: Change the workflow so that [AW Request Status] is not dependent on its own previous value. This can be done by controlling [AW Request Status] solely through the [Cancel Request] column and other logic not involving its own current value.
• Option 2: If you need [AW Request Status] to maintain a value that isn't "Canceled" until the checkbox is checked, consider using another helper column to store intermediate values or status changes that are then referenced by the [AW Request Status] column.

For example:

• Helper Column Setup: Add a column named [Previous Status] to temporarily hold the status before any cancellation. Update this column manually or through another part of your workflow that doesn't involve [AW Request Status].
• Modified [AW Request Status] Formula:

=IF([Cancel Request]@row = 1, "Canceled", [Previous Status]@row)

This setup ensures that there is no direct or indirect self-reference in [AW Request Status], preventing the circular reference error.

Casie

I tried the helper column and couldn't figure it out. What I ended up doing was using an automations to trigger this 😁 Thank you for your detailed answer