# Convert Formula to Plain Cell Value

Options

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?

• ✭✭✭✭✭✭
Options

I hope you're well and safe!

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

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Options

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

• Options

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!

• ✭✭✭✭✭✭
Options

How many rows do you have?

• Options

Currently about 170, could increase in the future.

• ✭✭✭✭✭✭
Options

Are you able to provide a screenshot for context?

• Options

Sure! Let me know if this helps.

• Options

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

• ✭✭✭✭✭✭
Options

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.

• Options

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.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!