Formula to add "No" to a blank cell, but leave value if not blank

SChristJLL · 11/11/21

On a Smartsheet project intake form, I have several Yes or No dropdown selections. If Yes, the user must answer additional questions. If No, they do not and those cells are left blank.

Auditors do not like Blank cells, so I'm looking for a formula to replace those blank cells with "Does Not Apply" but leave a value there if present

The formula needs to essentially do nothing if the User inputs "Yes" in the Business Unit Impacted, then enters values in the other 2 columns.

If they enter "No", I want to formula to return "Does Not Apply" to appease our Auditors.

Thanks!!

Andrée Starå · 11/11/21

Hi @SChristJLL

I hope you're well and safe!

You could use a Workflow combined with the Update cell Action.

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Antonio Figueroa · 11/11/21

I would create a duplicate column Have Impacted Bu Been Contacted and Contacts affected business unit and then in the duplicate column write an if statement. Remember you have to change the name a little cause smartsheets does not allow two columns with the same name.

=if([Have IMpacted BU Been Contacted] = "","Does Not Apply",[Have IMpacted BU Been Contacted]

then just hide the two columns above.

Andrée Starå · 11/11/21

Hi @SChristJLL

I hope you're well and safe!

You could use a Workflow combined with the Update cell Action.

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

SChristJLL · 11/11/21

Thanks Andre and Antonio!

Andre, I've thought of doing that possibly, but I have about 50 questions throughout this lengthy form aside from the ones in my screenshot.

That may be simpler than figuring out a formula though, I'll give it a try!

SChristJLL · 11/11/21

https://community.smartsheet.com/discussion/comment/310674#Comment_310674

Antonio, thanks for your suggestion. I don't think this would work for what we are doing though. After form is submitted, we are saving it as a PDF that is to be audited. I'm not sure the additional columns would help the cause.

Andrée Starå · 11/11/21

@SChristJLL

Excellent!

You're more than welcome!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Formula to add "No" to a blank cell, but leave value if not blank

Best Answer

Answers

Help Article Resources

Categories