Change a cell value workflow issue
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!
Best Answer

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/2482299changecellvalueinanautomatedworkflow. 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)
 What if you used a helper column, so that any time "Auditorium" was checked, you could display the value you're looking for?
 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."
 Auditorium
Answers

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

Hi Julie!
One is checkbox and one is text/number.

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?

This is the checkbox column workflow:
This is the text/number column workflow:

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/2482299changecellvalueinanautomatedworkflow. 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)
 What if you used a helper column, so that any time "Auditorium" was checked, you could display the value you're looking for?
 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."
 Auditorium

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!

You're welcome! Feel free to reach out if you run into additional questions.
Help Article Resources
Categories
Check out the Formula Handbook template!