Circular reference error help

=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

Answers

  • Bassam Khalil
    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.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • 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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!