How write formula to pull content from most recent row?

RebeccaZD

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!

RebeccaZD

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

Paul Newcome

I would suggest moving that top portion to a second sheet or into Sheet Summary fields. That way you can write formulas that will reference the entire column and pick up new submissions as they are made.

RebeccaZD

Yeah, I can't create a new sheet as I don't want any new assets (trying to stay under 100 in the workspace so I can replicate) - I am unfamiliar with sheet summaries, I'll look into that. Thanks!

Andrée Starå

Hi @RebeccaZD

I hope you're well and safe!

Here's more information about the Sheet Summary feature.

https://help.smartsheet.com/articles/2476786-define-your-work-with-sheet-summary

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!

Paul Newcome

Happy to help. 👍️

RebeccaZD

@Paul Newcome how would I write the formula if I was going to pull it into another sheet? I still can't figure out how to do it - my attempt below isn't working (with sheet above as "Goal 1 monitoring" and column I am starting with is Name:

=IF(AND({Goal 1 monitoring Most Recent} = 1, {Goal 1 monitoring shorthand name} = "Strategy 1A", {Goal 1 monitoring Name)

Paul Newcome

You would use an INDEX/COLLECT like so:

=INDEX(COLLECT({Column To Pull From}, {Most Recent}, @cell = 1, {Shorthand}, @cell = "Strategy 1A"), 1)

RebeccaZD

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

RebeccaZD

Nevermind got it - changed to date column -- THANK YOU!!!

Paul Newcome

Glad you were able to get it working and happy to help. 👍️