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
 63.9K Get Help
 410 Global Discussions
 219 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 143 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 298 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!