How to return "0" in a cell when another cell has been flagged

K.Nixon

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.

KPH

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.

KPH

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.

K.Nixon

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

KPH

Automations are wonderful for doing what’s impossible with formula. I’m happy to hear you have it working.