A way to collect the like data (email) from different Columns

Hi,

I have the need to extract a list from a sheet that will give me a list of all of the employees another employee is assigned to review.

My row contains is the name of the person who is submitting a review on others.

I have 5 columns that will contain the email address of the person who will be submitting a review. There are 106 rows that employees email can be listing on in any one of hte 5 columns.

I want a report or sheet that will give me the names of all the people that one reviewer has been assigned to:

Example - Who are all the people that DebbieP has been assigned to in all 5 columns on any rows.

I cannot figure this out and I've been on it so long I'm betting it's so simple I will kick myself!

Thank you!

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You would have to use a JOIN/COLLECT 5 separate times and "add" them together.

    =JOIN(COLLECT({Source Sheet Name Column}, {Source Sheet 1st 360 Column}, @cell = "DebbieP@gbi1914.com"), CHAR(10)) + CHAR(10) + JOIN(COLLECT({Source Sheet Name Column}, {Source Sheet 2nd 360 Column}, @cell = "DebbieP@gbi1914.com"), CHAR(10)) + CHAR(10) + JOIN(COLLECT({Source Sheet Name Column}, {Source Sheet 3rd 360 Column}, @cell = "DebbieP@gbi1914.com"), CHAR(10)) + CHAR(10) + JOIN(COLLECT({Source Sheet Name Column}, {Source Sheet 4th 360 Column}, @cell = "DebbieP@gbi1914.com"), CHAR(10)) + CHAR(10) + JOIN(COLLECT({Source Sheet Name Column}, {Source Sheet 5th 360 Column}, @cell = "DebbieP@gbi1914.com"), CHAR(10))

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    You would have to use a JOIN/COLLECT 5 separate times and "add" them together.

    =JOIN(COLLECT({Source Sheet Name Column}, {Source Sheet 1st 360 Column}, @cell = "DebbieP@gbi1914.com"), CHAR(10)) + CHAR(10) + JOIN(COLLECT({Source Sheet Name Column}, {Source Sheet 2nd 360 Column}, @cell = "DebbieP@gbi1914.com"), CHAR(10)) + CHAR(10) + JOIN(COLLECT({Source Sheet Name Column}, {Source Sheet 3rd 360 Column}, @cell = "DebbieP@gbi1914.com"), CHAR(10)) + CHAR(10) + JOIN(COLLECT({Source Sheet Name Column}, {Source Sheet 4th 360 Column}, @cell = "DebbieP@gbi1914.com"), CHAR(10)) + CHAR(10) + JOIN(COLLECT({Source Sheet Name Column}, {Source Sheet 5th 360 Column}, @cell = "DebbieP@gbi1914.com"), CHAR(10))

  • Debbie Petersen
    Debbie Petersen ✭✭✭✭✭

    I tried this out and I am assuming the Source Sheet Name Column would be the one shown above labeled Person to Submit 360. I am getting an #invalid ref error. Here's the formula


    =JOIN(COLLECT({TEST 2021- 360 Reviews Range 6}, {Source Sheet 1st 360 Column}, @cell = "DebbieP@gbi1914.com"), CHAR(10)) + CHAR(10) + JOIN(COLLECT({TEST 2021- 360 Reviews Range 6}, {Source Sheet 2nd 360 Column}, @cell = "DebbieP@gbi1914.com"), CHAR(10)) + CHAR(10) + JOIN(COLLECT({TEST 2021- 360 Reviews Range 6}, {Source Sheet 3rd 360 Column}, @cell = "DebbieP@gbi1914.com"), CHAR(10)) + CHAR(10) + JOIN(COLLECT({TEST 2021- 360 Reviews Range 6}, {Source Sheet 4th 360 Column}, @cell = "DebbieP@gbi1914.com"), CHAR(10)) + CHAR(10) + JOIN(COLLECT({TEST 2021- 360 Reviews Range 6}, {Source Sheet 5th 360 Column}, @cell = "DebbieP@gbi1914.com"), CHAR(10))

  • Devin Lee
    Devin Lee ✭✭✭✭✭
    edited 11/23/21

    Not sure if those columns are Contact Lists or Text/Number. If they are Contact Lists you can create a filter based on current user which is what I imagine you are trying to do so people can tell what they are assigned to. If not, you would need to create a filter for each email address used and have each person select their name in the filter. You can do the same thing with a report so the filter is already applied when they open it.


  • Debbie Petersen
    Debbie Petersen ✭✭✭✭✭

    Thank you Lee - that's what I'm trying to avoid as there are 106 ppl on the list and trying to create report for VPs to see who their employees are reviewing.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Did you make sure to create all of the cross sheet references?

  • Debbie Petersen
    Debbie Petersen ✭✭✭✭✭

    Got it to work! Thanks so much! I just needed the string once and it pulled all the names in!

    Thank you both so much and Happy Thanksgiving!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!