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!
Answers
-
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}
-
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β¦
-
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
Help Article Resources
Categories
Check out the Formula Handbook template!