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

Options

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?

«1

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    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"

  • Kevin Kirsch
    Options

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

  • CycleBagEd
    Options

    @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 ✭✭
    Options

    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
    Options

    @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.

  • Kevin_KK
    Options

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

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

    Yes - I just tested - restriction still exists.

  • SPT5687
    SPT5687 ✭✭
    Options

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

  • LouiseB
    Options

    Has anyone found a solution for this?

  • Cinema11
    Cinema11
    edited 05/18/22
    Options

    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.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    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

  • Marlei
    Marlei ✭✭✭✭
    Options

    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!!



  • Kevin Smith
    Kevin Smith ✭✭✭✭✭✭
    Options

    This chain is not so old that I can't add to it, but if the functionality is not going to allow for a formula, could we allow for the Change a Value to be set to the value of another field. This will not solve all the problems that a formula would solve but is also a very helpful use case for "Change A Cell Value".

    **Love the collaboration, always great ideas from the community!

    Thank you,

    Kevin

    Happy to help if I can.

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!