Pulling most recent entry into summary sheet

HRJ_207
HRJ_207 ✭✭
edited 03/20/25 in Formulas and Functions

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!

Answers

  • Hollie Green
    Hollie Green Community Champion

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

    DigitalFlows_Generic_Black.png

    Davide Musmeci, PhD

    Project Management Professional

    www.digitalflows.eu

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!