Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Is there a way to pull cells that meet criteria into individual cells in another sheet?

I'm working on a multi-sheet solution for my PMO and have been using Index and Match to pull specific information from a metadata sheet into matching cells on other sheet using INDEX/MATCH. I've been using either the Change Order (CO)number or the Scope Change ID (Larger Project Identifier) for most of the solution, but I've run into an issue where I need all of the CO numbers associated with that Scope Change ID to copy over into a new sheet.

I know that you could do this in a report, but there are some additional new fields that need to be on the sheet so I don't think generating a report is an option. Is there a variation on INDEX/COLLECT that would all each cell in the CO number column to be a unique value that matches the Scope Change ID?

Source Sheet:

Destination Sheet:

Best Answer

  • Community Champion
    Answer ✓

    JOIN(COLLECT()) will return all of the values into a single cell, but is kind of useless if you intend to pull individual CO data from the other sheet as well. Using CHAR(10) as a line break and wrapping the text is my favorite way to JOIN values.

    =JOIN(COLLECT([CO Number]:[CO Number], [Scope Change ID]:[Scope Change ID], [Scope Change ID (CO Number Match)]@row), CHAR(10))

    If they need to be on separate lines is a bit more difficult, because you would have to know how many (possible) CO's there could be for each Scope Change ID. So you could build out placeholders for each Scope ID like below. And if that particular Scope ID doesn't have the nth Change Order, it returns an error (which you could clean up with IFERROR if you'd like)

    =INDEX(COLLECT([CO Number]:[CO Number], [Scope Change ID]:[Scope Change ID], [Scope Change ID (CO Number Match)]@row), [CO Match #]@row)

    Jason Tarpinian - Sevan Technology

    Smartsheet Aligned Partner

Answers

  • Community Champion
    Answer ✓

    JOIN(COLLECT()) will return all of the values into a single cell, but is kind of useless if you intend to pull individual CO data from the other sheet as well. Using CHAR(10) as a line break and wrapping the text is my favorite way to JOIN values.

    =JOIN(COLLECT([CO Number]:[CO Number], [Scope Change ID]:[Scope Change ID], [Scope Change ID (CO Number Match)]@row), CHAR(10))

    If they need to be on separate lines is a bit more difficult, because you would have to know how many (possible) CO's there could be for each Scope Change ID. So you could build out placeholders for each Scope ID like below. And if that particular Scope ID doesn't have the nth Change Order, it returns an error (which you could clean up with IFERROR if you'd like)

    =INDEX(COLLECT([CO Number]:[CO Number], [Scope Change ID]:[Scope Change ID], [Scope Change ID (CO Number Match)]@row), [CO Match #]@row)

    Jason Tarpinian - Sevan Technology

    Smartsheet Aligned Partner

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions