Formula to lookup prior cell values

Peter Spung

...and not require use of the API*. Often, people want to create Smartsheets that use or track values of cells in a(nother) sheet over time, or look up a prior value using a date, or a date formula. The VLOOKUP function could be enhanced to do this, taking a date argument. Or, a separate function created to be used in formulas.

My use case: I have one grid that has a cell, "stacked ranking", which ranks in priority all items (product roadmap items). Those ranking (cell values) are edited, as the priorities change over time -- as things move up and down in ranked priority, from 1 to 20. (There are hundreds of items in the grid, but only 20 are ranked at the top.) I'm creating another grid that shows, for the current top 10 items, where each has been ranked over the past 6 months. To see if we're making adequate progress, or highlight any that have stalled. To do that, I need to lookup the prior values of the ranking cell, at today minus one month, today minus two months, ..., today minus six months.

Another use case: https://community.smartsheet.com/discussion/75191/last-weeks-value

*For many of us, the API is a heavy lift. Here is an explanation, and a related idea: https://community.smartsheet.com/discussion/100785/call-apis-from-a-formula/

Peter Spung

Here's an example of how I would use a formula to look up prior cell values:

Roadmap ID, Short Description, and Ranking Now come from another sheet. Ranking Last Month, Ranking Month-2, Ranking Month-3, etc, would lookup prior values of the Ranking, when a formula is available to do it. Possibly Stalled would have a formula that uses those prior Ranking values.

Andrée Starå

Hi @Peter Spung

I hope you're well and safe!

Here's a possible workaround or workarounds

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

Peter Spung

Hi Andree, that may work. Since I can't do a value lookup of a cell based on a date, an alternate approach might be to take a monthly snapshot of the values in a sheet, in order to display, compare or otherwise compute with them over time.

I'll drop you an email to get a sample, and see if a workaround is doable. Thanks! Peter

Peter Spung

Here is another example of how looking up prior cell values would be used: using the %complete value in a formula for project activities or product roadmap items at various points in time:

Erik Jensen

Did you guys figure out a solution to this. I am dealing with a similiar problem. Andree's seems unnecessarily complicated... But maybe that is the only choice...