Formula to Collate Entries based on Criteria

Cheryl Collins
Cheryl Collins ✭✭✭✭✭✭
edited 03/27/23 in Formulas and Functions


I'm looking for a concatenate type formula that will collate a number of line item updates based on their project ID.

I have a sheet that contains multiple tasks for a project, each having an update. I want to collate all the task updates, based on Project ID, into a rolled up view to give one overall update of the concatenated task updates.

Whats the best way of doing this? So, far, I've got a separate sheet that holds all the individual project IDs.

I need a formula that will collect the updates from the Task Sheet for all tasks, with a specific project ID, where the Status is either "In Progress" or "Completed" IF the Due Date is in the last 30 days.

I also need a formula that will match the central project ID and collate any comments against all Tasks on the Weekly Task Sheet where the Status = "In Progress" AND the Due Date is in the next 30 days

Weekly Task Sheet

Rolled Up Update Sheet





Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!