Change Cell Value Automation - Can I add a formula to the cell?

I want to create an automation that when I indicate that the row "type" is "section" the workflow automatically changes the value of the QUOTE column to a formula that is =SUM(CHILDREN())

When I've tried this, the workflow adds the formula as text rather than a working function. Is this possible?

«13

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Jason Dressel 

    Hope you are fine, With the Change cell value action in automated workflows, you can input data to text/number, single select dropdown, multi select dropdown, and checkbox columns. please read the following article it will help you to understand Change cell value action in automated workflows.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Hey @Jason Dressel I'm trying to accomplish the same. Were you able to change a cell to a formula using automation?

  • @Jason Dressel I am trying to do exactly the same thing to stop my users deleting the roll-up formulas in the parent rows. Very, very frustrating that a) sort doesn't work if rows are locked and b) you can't just lock individual cells.

    It's a real puzzle, Smartsheet. Just when I think I have something workable, a problem comes up.

  • JeffMc
    JeffMc ✭✭

    I'm also trying to do the same thing, and am frustrated that it's not possible.

    I don't see the risk of an automation writing a formula in a cell. Not sure why this is prevented.

  • Andrew Heitner
    edited 03/10/22

    @JeffMc @Thomas Cain

    It may not be a one size fits all solution, but if you make the column a function column, any new rows copied to that sheet does not overwrite your function.

    It also effectively locks the cell in that column as the value of that function.

    Hope that helps.

  • I see there is still the " ' " restricting formulas...

  • Ryan Perry
    Ryan Perry ✭✭
    edited 04/18/22

    Yes - I just tested - restriction still exists.

  • SPT5687
    SPT5687 ✭✭

    Attempting to do the same thing here... I need to either copy the value from another cell into a new column/cell from the automation trigger, or use a formula to reference the value.

    I.e.

    If my Y/N flag in my "SprintMatch" row changes to N, then copy the value in the "RemainingHours" row to a new column (RecordedSprintHours; name isn't important).

    Seems like should be simple but struggling with this task....

  • Has anyone found a solution for this?

  • Cinema11
    Cinema11
    edited 05/18/22

    I am attempting to do the same thing. If a value changes in one field, I want another field to refresh based on the logic defined in the formula in that field.

    My formulas do all the heavy lifting, but they do not refresh without opening the Smartsheet and re-saving. Effectively, all I need is automation to "run" the formula in a specific field on a set interval; in my case, daily..

    I see a reference in one of the help articles that says the following:

    • "The change cell action can input new values in cells that contain cell formulas, but the workflow will not run if you convert a cell formula to a column formula"

    But it also says this:

    • "You must specify the exact value that you want to input in a cell. At this time, it is not possible to add a formula to a cell, reference the value in another cell, or reference the value in a sheet summary field. "

    [ From: Change the Value of a Cell in an Automated Workflow ]

    Which i interpret to mean that formulas in automated work flow are not supported at this time.

    Atlassian, please support this functionality.

  • Hi @Cinema11

    There are a few solutions here in the Community that can help you "refresh" your sheet daily so your formulas can run at the correct interval.

    Here's an example solution from @Andrée Starå:

    Daily Update Solution without the need to open the sheet, use Zapier, or similar

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Marlei
    Marlei ✭✭✭✭

    I also have a use case where I need the functionality of being able to add a formula using change cell automation. Currently the work around is having to create two additional helper columns to get the result.

    First helper column is a manual entry field. Second helper column is the formula to pull in the unique id if "Project" is selected in the other column. Then another formula is used in the data column to pull whichever field is not blank.

    This works, however it is not optimal.

    Please add this functionality!!



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!