Formula that updates Blank cells, ignores data when present


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
    Sherry Fox ✭✭✭✭✭✭

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

    Sherry Fox

    Business System Data Analyst

    Del-Air Heating, Air Conditioning, Plumbing and Electrical

    EAP | Mobilizer | Automagician | Superstar | Community Champion

  • Brent Wilson
    Brent Wilson ✭✭✭✭✭


    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.

    Brent C. Wilson, P.Eng, PMP, Prince2

    Facilityy Professional Services Inc.

  • SChristJLL
    SChristJLL ✭✭✭

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

  • SChristJLL
    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!