If cell is blank, make it 0.

gjohnson.pcpro · 05/18/22

Hello all.

I have a sheet with numerous cost fields. When a user enters a new row, I would like these cost fields to be set to 0 if the user leaves them blank. For example, I have a column called Overhead Cost. If the user fills in every cell in the row but leaves Overhead Cost blank, I would like that cell to automatically show 0.

I've tried using the following formula, but it just returns "Circular Reference".

=IF(ISBLANK([Overhead Cost]@row), 0)

For the record, if the cell is NOT blank, I just want to leave it alone. Whatever value the user entered should remain as is.

Any assistance in getting this formula to work properly would be appreciated.

Thank you!

Mike TV · 05/18/22

@gjohnson.pcpro

Instead of a formula, you'll want to set up an automation for "when row is added" with a Condition of "Overhead cost" is blank, with an Action of Change Cell to "0".

Mike TV · 05/19/22

@gjohnson.pcpro

The problems with using a formula is that a column formula cannot accept a random value to be entered. A cell formula would need to be copy-pasted in manually each time. So having a formula where you want manual input isn't ideal or possible in some cases. That's why we need to use an automation to achieve our goal.

Mike TV · 05/18/22

@gjohnson.pcpro

Instead of a formula, you'll want to set up an automation for "when row is added" with a Condition of "Overhead cost" is blank, with an Action of Change Cell to "0".

gjohnson.pcpro · 05/19/22

Thank you, Mike. I would have preferred to apply a formula to each column, but this workaround gets the job done. I appreciate your help!

Mike TV · 05/19/22

@gjohnson.pcpro

The problems with using a formula is that a column formula cannot accept a random value to be entered. A cell formula would need to be copy-pasted in manually each time. So having a formula where you want manual input isn't ideal or possible in some cases. That's why we need to use an automation to achieve our goal.

gjohnson.pcpro · 05/19/22

Understood. Thank you again for your help. Really appreciate it.

Dan W · 05/19/22

@gjohnson.pcpro

If they are inputting data using a form you can set the form field to default as 0 unless changed.

Dan W · 05/19/22