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

Swapna Kasturi
edited 03/07/21 in Formulas and Functions

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

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.

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.



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!