Change a cell value workflow issue

Sidonia Posteuca

When I use the Change a cell value action under Automations it always puts a ' in front of the value when in the sheet.

Why is this?

For example these are the results:

If I put a number it comes out '7.0

If I put a formula it comes out '=SUM([Catherization Labs]@row:[Infection Control]@row)

I don't know how to get rid of this and I have tried many times. Please help!

Julie Fortney

Thanks for the screenshots! I tried recreating those workflows myself and ran into the same issues. This help article explains why: https://help.smartsheet.com/articles/2482299-change-cell-value-in-an-automated-workflow. You can't add formulas using this type of workflow. The article doesn't specifically state this, but it appears you can only update a checkbox to checked, unchecked, or a custom text value. It's not able to recognize your value as a number.

I think you could accomplish what you're looking to do in a simpler way.

• Auditorium
  • What if you used a helper column, so that any time "Auditorium" was checked, you could display the value you're looking for?
    • Here's the formula you could use in the helper column: =IF(Auditorium@row = true, 3)
    • If there are other criteria that affect the value you need (e.g. if Auditorium is checked and Infection Control is 2, display the number 7), you can include that in the formula. =IF(AND(Auditorium@row = true, [Infection Control]@row = 2), 7)
    • Be sure to make these column formulas (see instructions below if you're not familiar with this)

• Factor
  • If you always want to apply that formula to every row that's added, you can just make it a column formula. Enter the formula anywhere in the column, then right click and select "Convert to Column Formula."

  
  

Julie Fortney

Hi Sidonia,

I've not run into that issue before. What is your column type for the cell you are trying to change?

Sidonia Posteuca

Hi Julie!

One is checkbox and one is text/number.

Julie Fortney

I thought it might be related to your column types, but I just tried a test workflow to update a checkbox column to a number, and it worked for me. It didn't add a ' in front of the number.

Could you take a screenshot of your workflow?

Sidonia Posteuca

This is the checkbox column workflow:

This is the text/number column workflow:

Julie Fortney

Thanks for the screenshots! I tried recreating those workflows myself and ran into the same issues. This help article explains why: https://help.smartsheet.com/articles/2482299-change-cell-value-in-an-automated-workflow. You can't add formulas using this type of workflow. The article doesn't specifically state this, but it appears you can only update a checkbox to checked, unchecked, or a custom text value. It's not able to recognize your value as a number.

I think you could accomplish what you're looking to do in a simpler way.

• Auditorium
  • What if you used a helper column, so that any time "Auditorium" was checked, you could display the value you're looking for?
    • Here's the formula you could use in the helper column: =IF(Auditorium@row = true, 3)
    • If there are other criteria that affect the value you need (e.g. if Auditorium is checked and Infection Control is 2, display the number 7), you can include that in the formula. =IF(AND(Auditorium@row = true, [Infection Control]@row = 2), 7)
    • Be sure to make these column formulas (see instructions below if you're not familiar with this)

• Factor
  • If you always want to apply that formula to every row that's added, you can just make it a column formula. Enter the formula anywhere in the column, then right click and select "Convert to Column Formula."

  
  

Sidonia Posteuca

Wow. I did not know about the "convert to column formula" option. That is amazing!

I think I will create those numerical columns like you have displayed and use formulas instead of the workflows. It will extend the sheet quite a bit but I think it is a better result in terms of automating the sheet.

Thank you very much!

Julie Fortney

You're welcome! Feel free to reach out if you run into additional questions.