If cell is blank, make it 0.

Options

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!

Best Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓
    Options

    @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
    Mike TV ✭✭✭✭✭✭
    Answer ✓
    Options

    @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.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!