Last Week's Value

Cierra Ray
Cierra Ray ✭✭
edited 01/22/21 in Formulas and Functions

Hi All,

Does anyone know of a formula that can pull the values from an existing column from the week prior using the cell history or activity log?

e.g. I can see the value last week in the cell history but I want to pull it automatically via a column to create a last week vs this week view. My sheet is not date based so I cannot simply use a formula to find a prior date's value.

Thank you,




  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Cell History and Activity data are not accessible data in smart sheet and can't be captured and shared in the sheet dynamically.

  • JeremiahHorstick
    JeremiahHorstick ✭✭✭✭✭✭

    @Cierra Ray

    This can be done with the API.

  • Peter Spung
    Peter Spung ✭✭✭

    How does one use the API from a formula?

    Or asked another way... The API is a heavy lift for most of us. We have to get approval to use a language coding platform that our org supports, be granted API access to sheets, then have test tools to debug, then learn an additional language (we already learned the formula language), etc. We already have in Smartsheet the approved authority to use formulas, and know how to use them. And, we want the result of an API call to end up back in a Smartsheet cell, not in the memory space of the language environment. Just like this question to the community -- "I want to lookup a prior value of a cell in this or another sheet, on this date (or formula), and return the result in this cell where the formula is located". ...Why not make every API function available in a formula?... Once decided, that's indeed a heavy lift for Smartsheet. That could be done in phases -- starting with the most requested functions, or most used APIs (Smartsheet undoubtedly tracks API use in its website logs).

    No offense intended -- I observe this answer quite often in the Community -- "Just use the API". Doing so should be made a lot simpler, and a lot more accessible.

    Any thoughts? Peter

    P.S. Yes, I will write an enhancement request for this.

  • Peter Spung
    Peter Spung ✭✭✭

    Two new product enhancement requests are in the hopper:

    Provide a formula to look up prior cell values:

    Make APIs available as formulas:

    Thanks, Peter Spung | | @paspung

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!