How to return "0" in a cell when another cell has been flagged
I created a timesheet and a form to go along with it. When someone uses the form and flags that someone has called off, I want the flag to return zeros in the ST Hrs, OT Hrs, and DT Hrs cells. Using AI to generate the formula below, it returns a Circular Reference error when the formula is placed in the ST Hrs cell.
Best Answer

The formula you have needs to go into the ST Hrs cell, it says:
If Called Off is flagged then put 0 here. If not put ST Hrs here.
If you paste this into the ST Hrs column, you will be creating a circular reference. The formula will be trying to return itself as it is in ST Hrs. In other words, if Called Off is not flagged use this formula, that says if Called Off is not flagged use this formula, that says if Called Off is not flagged use this formula, etc., etc.
You could create another column and place the formula you have in the new column. This new column would then show 0 if Called Off is flagged and ST Hrs if not. Then you can hide the old ST Hrs column to avoid confusion.
Alternatively, you could set up an automation to replace the value in a cell (either with 0 or you could clear the cell) when Called Off is flagged. Let us know if you need help with this.
Answers

The formula you have needs to go into the ST Hrs cell, it says:
If Called Off is flagged then put 0 here. If not put ST Hrs here.
If you paste this into the ST Hrs column, you will be creating a circular reference. The formula will be trying to return itself as it is in ST Hrs. In other words, if Called Off is not flagged use this formula, that says if Called Off is not flagged use this formula, that says if Called Off is not flagged use this formula, etc., etc.
You could create another column and place the formula you have in the new column. This new column would then show 0 if Called Off is flagged and ST Hrs if not. Then you can hide the old ST Hrs column to avoid confusion.
Alternatively, you could set up an automation to replace the value in a cell (either with 0 or you could clear the cell) when Called Off is flagged. Let us know if you need help with this.

The automation worked, thanks so much! I always forget about that.

Automations are wonderful for doing what’s impossible with formula. I’m happy to hear you have it working.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!