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.

How do I join specific cells from multiple sheets?

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.

Best Answer

Answers

  • Community Champion

    @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)),", ")

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

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

  • 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!

  • Community Champion
    Answer ✓

    @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:

    Site faviconCreate efficient formulas with @cell and @row | Smartsheet Learning Center

  • 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..

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

  • I have a pretty basic sheet that I am using to develop a formula, and am encountering an issue I haven't seen before. The formula isn't including my second row for some reason. It isn't a huge issue f…
    User: "jjg279"
    Answered ✓
    9
    2
  • I'm sure that this is an easy fix but I've tried a bunch of different ways and can't get this to work. I need to get the max date from a sheet to feed it into my meta data sheet (dates are stored in d…
    User: "susanmgfin"
    Answered ✓
    8
    2
  • I need help to come up with the risk formula. I tried many different formulas and tried to modify it but just can't seem to have desired results. So these are the conditions I MUST meet: Program is a …
    User: "Ronak"
    Answered ✓
    29
    6