Formula to lookup prior cell values

Peter Spung
Peter Spung ✭✭✭
edited 02/08/23 in Smartsheet Basics

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

*For many of us, the API is a heavy lift. Here is an explanation, and a related idea: