How write formula to pull content from most recent row?


New to Smartsheet - excited for help. I've set up dashboards to pull the most recent submission data from locked cells on top of my sheet (gray formatting in image). I want to copy the content from the form submission rows below that have "most recent" box checked, based on matching the "shorthand name" column. So the locked grayed row for Strategy 1A (row 2) would update with the content from the most recent submission data for Strategy A (row 7).

A couple of questions:

  1. What formula will return values based on matching shorthand and only from those with most recent checked? I've tried variations of IF(AND, MATCH, INDEX, etc. and am struggling.
  2. How can I get the form submission date column to update with most recent, given I can't insert formula in that column?

Appreciate the help!

Best Answer

  • RebeccaZD
    RebeccaZD ✭✭✭
    Answer ✓

    So close - I'm getting invalid column value when I use this:

    =INDEX(COLLECT({Column to pull from}, {Most Recent}, @cell = 1, {Shorthand}, @cell = "Strategy 1A"), 1)

    Any ideas??!!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!