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

Jason Dressel

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?

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.

Kevin Kirsch

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

CycleBagEd

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

Thomas Cain

The answer that Khalil had didn't answer the question. Question is still outstanding.

How does one use Change Cell automation to insert a formula?

My guess is they purposely put an " ' " to block the functionality. An explanation of why this is so would and is there a timeframe whereby such restriction will be removed would be a respectful answer.

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

@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

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

Ryan Perry

Yes - I just tested - restriction still exists.

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

LouiseB

Has anyone found a solution for this?

Cinema11

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.

https://community.smartsheet.com/discussion/comment/330165#Comment_330165

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

Cole Lusty

Hi @Genevieve P. - This is still not possible and it seems like there is a lot of interest in this functionality. Could this be prioritized?

Thank you!

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

Kevin Smith

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!