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
-
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:
- 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. - 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 (usually1
for checked and0
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.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"
- 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,
-
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
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!