If cell is blank, make it 0.
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
-
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".
-
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
-
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".
-
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!
-
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.
-
Understood. Thank you again for your help. Really appreciate it.
-
If they are inputting data using a form you can set the form field to default as 0 unless changed.
-
If they are inputting data using a form you can set the form field to default as 0 unless changed.
-
Thank you, this is very helpful.
How do you ensure this applies to data previously entered into smartsheet.
This automation works for new data changed or edited but not seeing for previous as well as new.
Thank you
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!