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
 10.5K Get Help
 61 Global Discussions
 46 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!