Convert Formula to Plain Cell Value

young.benjamin

Hi there!

For this question, I have my formula figured out. Basically, I need PMs to enter a value into a cell on a weekly basis and have that value recorded for historical record. I have that piece working, using an if statement based on the week number.

I'm just having issues getting the actual history part to work. Because I'm using IF statements, when the sheet rolls to a new week, the previous data obviously gets overwritten, so I need to convert the result to a plain value. Any way to do this?

Andrée Starå

Hi @young.benjamin

I hope you're well and safe!

Please have a look at my post below with a method I developed.

More info: 

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 support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

@young.benjamin Here's a method I developed to capture historical data. Basically you use a copy row automation triggered when the specific action happens (or date is reached or whatever other trigger you need), then you use a unique identifier and an INDEX/MATCH to pull the static data back over from the sheet the row was copied to.

https://community.smartsheet.com/discussion/63561/date-time-stamp-option

young.benjamin

Thanks @Paul Newcome and @Andrée Starå!

Both of the referenced solutions are on the right track, but not very scalable to what I need. We're planning a year's worth of metrics in each sheet, by week, so 52-53 columns.

Maybe this will help clarify - here's the formula: =IF([7/17]2 = WKNUM2, Actual@row, "PENDING")

Where 7/17 is the current historical column and WKNUM 2 is my counter cell for what week out of the year it is. Actual@row is where the data is actually entered. I just need the value/outcome of this formula to be kept and the formula to be taken out, so that when the WKNUM counter increases, the data doesn't go back to pending.

Maybe this isn't possible without doing one of the two methods listed. Thanks in advance!

Paul Newcome

How many rows do you have?

young.benjamin

Currently about 170, could increase in the future.

Paul Newcome

Are you able to provide a screenshot for context?

young.benjamin

Sure! Let me know if this helps.

young.benjamin

When WKNUM bumps to 30, 7/24 rows will change to actual data and 7/17 rows will change to PENDING.

Paul Newcome

You are going to have to leverage the API or the premium add-on Bridge. I'm not sure if Zapier or some other third party app would be able to handle that.

My suggestion would be to have everyone fill out the appropriate week column and then use a formula to pull in the Actual.

young.benjamin

Got it. Thanks Paul. That was our original direction, but just thought I'd try to make it easier on the guys filling it out not to have to side scroll all the way through the sheet. I guess hiding columns will work just as well. Appreciate the help.