Crew Data base match/index multiple

Jacob Mizer · 01/19/21

So i have a crew database which has every team member with position, crew, start date and day of birth. I use this to pull service anniversaries and month of birth day wishes on our plant main dashboard. I was trying to make a crew list by pull information form the crew database but when i do the match/index formula it only pulls the 1st name, then i also tried to do join/collect but I get every team member on that crew in one cell. I thought of doing vlookup but I don't know if that will work either. Does anyone have a solution out there?

Andrée Starå · 01/19/21

Hi @Jacob Mizer

Not sure I follow!

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Jacob Mizer · 01/19/21

Crew Position.PNG

So Database one is crew position.

Crew List.PNG

Then I would like to pull information from database one to make a crew list. So all I have to update is database one. I have match/index formulas for the team leaders and Supervisors but when it comes to the rest of the crew it hard to get multiple points to come up.

I tried join/collect for A crew but all the names so up in one cell. Is there a why to have them come up in separate cells?

@Andrée Starå

Andrée Starå · 01/19/21

@Jacob Mizer

I think you'll have to use JOIN(COLLECT and then separate the values again in the Crew List sheet.

Make sense?

I'll get back to the post with another post describing how to structure it.

Jacob Mizer · 01/19/21

@Andrée Starå

It does I did the join(collect but I don't know have to separate out the values.

S_Karkhoff · 02/05/21

@Jacob Mizer & @Andrée Starå -

I have a similar use case. I have one sheet (Sheet A) that is a list of staff used to moderate virtual events in one sheet with their email, name, contact info, etc. On another sheet Sheet B) I have a multi-select dropdown where they can select the moderator name(s) (dropdown with text, not a contact list). I am trying to create a formula that will pull the contact information from Sheet A into Sheet B based on the selections from the dropdown list on Sheet B.

I've gotten to the point where I can bring them over using this formula:

=JOIN(COLLECT({Contact Column}, {Full Name M}, HAS([Moderator Name]@row, @cell)), CHAR(10))

in the above, contact column is the contact column (multi-select contact) from Sheet A, Full Name M is the Name column from Sheet A (multi-select dropdown) and Moderator Name is the multi-select dropdown from Sheet B where the moderators are selected from a dropdown list.

At first it seems to work, and the contact fields show up:

But - then when I save, it reverts back to only showing emails. What am I doing wrong?

Jacob Mizer · 02/05/21

@S_Karkhoff I think because the column is not in contact format. What might work and this is a big MIGHT. Is have a multi drop down to select the Moderator(s) with no formula in the column then add a second column with your formula to pull contact information from the Moderator(s) column but use the contact format on that column. You get what I'm saying. I am not Smartsheet expert but I did stay at a Holiday Inn last night. I hope it works

S_Karkhoff · 02/05/21

https://community.smartsheet.com/discussion/comment/278435#Comment_278435

No luck yet, but thanks for the laugh at least! Hope the Holiday Inn treated you well!

Crew Data base match/index multiple

Answers

Help Article Resources

Categories