Formula that updates Blank cells, ignores data when present

SChristJLL

Hello everyone!

Stumped trying to figure out a formula for our Pre Task Plan Smartsheet.

We have a form submission process that users answer questions for their project. If they answer Yes, then they are asked to provide more information on 2 additional questions. However if they answer No, the other 2 questions are hidden and they don't need to answer. This causes those cells to be left blank, which auditors HATE.

I need a formula that will update a blank cell to read "Does Not Apply" based off another cell saying No. However, I also need that same formula to ignore any data that is present if the answer is Yes.

I basically need the blank cells populated with "Does not Apply" when No is selected and do nothing if Yes is selected. Is this possible??

Thanks in advance for any tips!

Sherry Fox

Could you just use Conditional Formatting? If That Column is NO, then next 2 cells shows as "Does Not Apply"?

Brent Wilson

@SChristJLL

A simple If Statement would work for the Tasks Column but it becomes an issue with the dropdowns in the next column. You cannot have a column formula and allow the user to select a value.

You could have it as a cell formula and if they select it will overwrite

You may want to do a Hybrid and as @Sherry Fox stated

You could put this in the Task Column

=IF([4. Slip and Trip Hazards] = "Yes", "Slips, Trips, Falls", "Does Not Apply - No Controls Req.")

And then have a conditional format change the cell for the controls to grey or black if a No is selected.

SChristJLL

Thanks for the suggestion Sherry! I don't think Conditional formatting will actually change any cell values, just colors etc.

SChristJLL

Thanks for the input Brent! Ya, I have about 30+ questions like this on the form. The user first selects Yes or No, then provides details in Free Form Text, and then Selects from a dropdown list.

I'm starting to think I may need to add Automation workflows for each one of these which will be a pain, but seems like it will work.