Formula to paste values generated by function

@BigNate

I'm trying to figure out an automatic way to run a function in a cell, and have that function paste the value, and only the value, to that cell. I don't want the formula to continue to exist within the cell. Similar to if I copied the cells post calculation, and did a paste-special of only the values.

For example, =5+3 would set the cell value to 8, but if I enter the cell again, the =5+3 still exists. I only want 8. For my task however the data in the cell would come from a cross sheet VLOOKUP, and would be more than just a digit. Unfortunately the source data is constantly reorganized, so I need to be able to do the VLOOKUP and not worry about it updating with incorrect values.

In excel I've done this using VBA, with =SetCellValue(cell, value), but I have yet to find a way to do recreate this in Smartsheet.

Thanks for any help.

Andrée Starå

Hi Nate,

Unfortunately, it's not possible at the moment, but it's a great idea!

Please submit an Enhancement Request when you have a moment.

A workaround could be to use a third-party service like Zapier to store the value in the cell.

Would that work/be an option?

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Mike Wilday

 I also would look into using Zapier (A third-party service that costs extra). You might have to have a helper column that contains the formula which you could hide, then you might be able to use Zapier to copy the value into a new cell. 

Brendan Farren

Hi Nate,

I found a workaround which was helpful for my use case, hopefully will be for yours as well.

In my main worksheet, I create an automation to copy rows to a new sheet upon a trigger (in my case a form submissions adding a new row, but in your case it could be a value change or another trigger).

So I then have two copies of the data: 1 sheet with the formulas intact (whose results are frequently changing) and another sheet with the static copies of the rows and the initial formula results.

I would love to see this added as a feature: would let us see a 'point in time' record based on initial formula results, instead of always recalculating based on changing data.

Cheers,

Brendan

Andrée Starå

Hi Brendan & Nate,

@Brendan Farren Great workaround!

We can take that workaround and also have the value show in the main sheet.

Have a look at my post here to see how.

https://community.smartsheet.com/discussion/68589/lock-or-store-date-value-solution-without-using-zapier

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!