How do I create a ONE to MANY mapping cross-sheet formula/mapping?

I have a sheet called a "Catchall" which is the FULL list of projects being worked on in the company, and for every project on the list, there is a related OKR, start date, end date, health score etc

Screen Shot 2021-03-06 at 10.47.24 PM.png

I need to be able to pull all related projects that share a COMMON objective into a different sheet. The goal being to be able to get an average score per objective. EXAMPLE in the case above, I would be able to create a new sheet in which by "querying" for the NEW Objective "OKR1", I can pull in Project A and Project B and their report card points, 10 and 10 and average them to get 10.

Screen Shot 2021-03-06 at 10.51.40 PM.png

The reason I cannot use a report to do this is all the groupings don't reflect on the dash and I can't manipulate data in a report. How can I create this one to many mapping in sheets?

FYI tried VLOOKUP and JOIN COLLECT and didn't seem to work as I couldn't insert separate items under a single OKR into different rows.

Find more posts tagged with

Formulas

Accepted answers

All comments

Swapna Kasturi

Malaina Hudson

Hi, @Swapna Kasturi ,

Could you use AVERAGEIF to accomplish this? This wouldn't require that you list each project in your summary, just the criterion that you're matching to, and then AVERAGEIF would search for the condition and average the grades.

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.