How do I join specific cells from multiple sheets?

TBurnett

The question seems simpler than the outcome I'm hoping for. I need to combine distinct project types from multiple columns on multiple sheets. I know I need to use Join, Collect, Distinct, and potentially others, but I'm struggling with making it work the way I want it to. This is what I have:

This sheet will be duplicated each year to track projects at different locations. I want to be able to pull the distinct "Project Types" together without duplicates so we can see the different projects at the locations over time without the same project that carried over into the next year being counted twice. There will be "Project Type", "Project Type 2", and "Project Type 3" for each location each year, so just collecting the whole column doesn't seem like it would work. As I said above, I'm trying to combine multiple cells from multiple sheets. I'm creating a sheet to use as a hub for this information which is where the formula I'm trying to create will go.

=JOIN(DISTINCT(COLLECT({2024 Project Type}, Branches@row={2024 Branches@row}))", ")

I feel like I'm probably way off. Any direction or advice is appreciated.

Leibel S

@cell is just a way to reference the cell of the range that is being looked through (in this case the criteria range {2024 Branches}

See link below for more information:

https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

Leibel S

@TBurnett

I am not clear on the full picture you are looking for, but that formula should probably be:

=JOIN(DISTINCT(COLLECT({2024 Project Type},{2024 Branches@row},@cell=Branches@row)),", ")

TBurnett

I finally got my formula to respond. I changed it to:

=JOIN(DISTINCT(COLLECT({2024 Project Type}, {2024 Project Type}, Branches@row = {2024 Branches})), ", ")

TBurnett

https://community.smartsheet.com/discussion/comment/466169#Comment_466169

That looks very similar to what I finally got to respond. I'm curious about what the "@cell=Branches@row" does. I was thinking my ranges for collect would have to be the "2024 project type" on both. Can you explain the difference between using these different ranges?

Thank you for your response and help!

Leibel S

@cell is just a way to reference the cell of the range that is being looked through (in this case the criteria range {2024 Branches}

See link below for more information:

https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

TBurnett

That worked!! You're amazing.

Now here's the kicker. How can I make this formula drag-able? I need to apply this to 60 something rows and it's tied to the row now because the {2024 Branches@row} reference is only to one cell and the reference won't change when I drag the formula. Is there a way to make it compare "Branches@row" to the whole column in the reference sheet to find the correct row instead of matching the cell directly to the cell? Otherwise I'm going to have to make 180 different references. 🙃

=JOIN(DISTINCT(COLLECT({2024 Project Type},{2024 Branches@row},@cell=Branches@row)),", ")

I know that question sounds confusing..