Formula to Collate Entries based on Criteria

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

TIA

Cheryl

Find more posts tagged with

Formulas

Accepted answers

All comments

Paul Newcome

Have you looked into creating a row report and then grouping by project id?

Anson Cheung

Hi Cheryl,

Not quite sure if this is what you want. You can use JOIN() + COLLECT() to select by criteria and concatenate them in a cell for an overall update. E.g.

--Anson

Cheryl Collins

Hi @Paul Newcome

I need to get the concatenated update then into another sheet. From a row report, I don't think I'm able to do this, can I?

Thanks

Cheryl

Cheryl Collins

Hi @Anson Cheung

Your formula is great, thank you so much for your help.

Cheryl

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.