Pulling most recent entry into summary sheet

I'm pretty new to Smartsheet. I have created a Project Summary Sheet for each of my projects that pulls data from multiple sheets. Some of the fields pull from a Status Update Sheet which contains form entries of status updates. I need the Project Summary Sheet to only pull data from the top row (or most recent) of the Status Update Sheet. For the purposes of this post, we can focus on the "Percent Complete" column of the Status Update Sheet. I tried this formula (admittedly, with help from AI) and it's not working:

=INDEX(COLLECT({Status Update Sheet Percent Complete}, {Project Number}, NOT(ISBLANK(@cell))), 1)

Any guidance would be immensely appreciated. Thank you in advance!

Find more posts tagged with

Accepted answers

All comments

Hollie Green

If you have a created date you can use that to make sure it brings in the most recent. Add to your Collect formula

,{Created Date},Max{Created Date}

HRJ_207

Hi Hollie- thanks so much for responding!

I did try adding the date logic to the formula, without success. The date in my source sheet is called Modified, so I updated it as such. I also had the wrong Primary Column listed the first time; it is not Project Number in that sheet, but Summary Update Status.

Since the most recent update will always be in row 1 of the source sheet, I tried this formula: =INDEX(COLLECT({Status Update Sheet Percent Complete}, {Summary Update Status}, NOT(ISBLANK(@cell ))), 1)

I'm just getting an INVALID REF error now…

David3_DigitalFlows

Hi @HRJ_207,

i'm leaving here an example of a formula wich collects the Metric XX, when the check box [Current Week] is checked.

=IFERROR(INDEX(COLLECT([Metric XX]:[Metric XX], [Current Week]:[Current Week], 1), 1), "")

Hope this helps.

Best,

Davide

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.