Can contacts be pulled from another sheet based on certain criteria in the main sheet?

I am trying to write a formula that pulls in multiple contacts from 1 sheet into another. The current set up I have is in the main sheet I have 5 columns that have dropdown lists in them with up to 21 selections. Based on the teams that are selected in those columns I would like to populate 5 new columns that have contact information for the chosen teams. In a separate sheet I have the teams broken out into their own columns and then the specific contacts are in the cells below.

So far I have tried this formula to pull in the contact list in a column that only has 1 team selected:

=IF([ACCOUNTABLE TEAMS]@row = "NTL Strat -C2/CIV", INDEX(COLLECT({Email Recipients by Dept RASCI Range 1}))) but I am receiving an Incorrect Argument Error.

But this formula needs to account for multiple teams being selected within the dropdown list.

Any help or fixes would be greatly appreciated!

Find more posts tagged with

Accepted answers

All comments

Dan Beres

Hi kendallmar,

It's hard to fully write out the formula for you without having the ranges and criteria and formatting. To answer why you are receiving #INCORRECT ARGUMENT would likely be due to the COLLECT function you are using. COLLECT Function | Smartsheet Learning Center

COLLECT requires 3 arguments at minimum:
1) The range you want to display/collect from
2) The range you will evaluate against the criteria (3)
3) The criteria you want to check (2) against.

I am not 100% sure the amount of data you want to put in the sheet, but helper columns could likely simplify this process without having to hard code a bunch of different teams into the formula and if you decide any going forward it should be able to grab all of them. Just be aware that Smartsheet has a 500,000-cell limit - meaning every column you add reduces the number of rows you can have in your sheet and vice versa.

With how you described your formatting I assume the "Team Sheet" would look something like this?

Having them listed in columns is a bit more challenging to get working in formulas but I will try my best. There's no actual value to read or compare against as far as I know, so you would have to hard-code it similar to this:

=IF([ACCOUNTABLE TEAMS]@row = "NTL Strat -C2/CIV", JOIN(DISTINCT({Email Recipients for NTL Strat})," "), [insert more nested IF statements for each different team], [However you want the last IF statement to end, usually a blank]

If there was a value to compare against in this way, we could make a straightforward function that would be able to adjust whatever values you want it to.

For this one I could just do

=JOIN(COLLECT({Contact/Email Range},{Team(s) Range},{Team value in a cell/dropdown})," ")

What this formula is doing taking anyone who has that team value in the cell dropdown and then separating them out with a space. This makes it so the team contact info can be listed in a single cell which works for automations or just displaying it succinctly.

The downside for this one though is if someone was on multiple teams and you had them as "Team 3" and "Team 4" they would not show up in either, but I am sure we could find a workaround for that.

Hopefully this is helpful, and if you can give a bit more detail on how things are organized, I can help with a more precise answer for your specific scenario.

kendallmar

Hi @Dan Beres Thanks so much for the quick response! Here are some screenshots of how my sheets are set up for your clarity.

This is the Main Sheet that the formula will be entered in and those contacts will be used for an automation to send emails. On the left side of these columns is a task for the team to either be responsible, consulted, supporting, etc on.

As a reference these are the columns that the formula will be entered to populate the email contacts

Here is a screenshot of the 2nd sheet I have with the contacts/teams. Per your suggestion above I shifted the setup of the sheet so the teams aren't in individual columns and instead it is a team column and a contact column.

I tried your formula of =JOIN(COLLECT({Contact/Email Range},{Team(s) Range},{Team value in a cell/dropdown})," ") but when there are multiple teams selected in the Responsible Teams column then nothing is populating.

Let me know if there is any additional sheet setup information that would helpful to you to help craft this formula!

Again thanks so much for your help!!

Dan Beres

Thanks for the clarification!

I made a dummy sheet; it doesn't have the cross reference in it, but it should theoretically work the exact same since it is just looking at the range.

Hopefully this screenshot above makes sense; and yes, the issue I had with the other one was that it would be searching for an exact match / having both. If we use:
=JOIN(COLLECT(Contact:Contact, Team:Team, CONTAINS([RESPONSIBLE TEAMS]@row, @cell)), " ")

It will look for anything that contains whatever is in the Responsible Teams column. I just copy+pasted the formula and changed the word in that green text box to ACCOUNTABLE, SUPPORT, CONSULTED, and INFORMED and it worked. You can see in the first row of the CONSULTED TEAMS / Email columns that if someone is present on multiple teams, it will grab their user even if only one team is present in the cell.

This is a simple/straightforward formula for it, but because of its low complexity it does have one major issue - if you have a team that is named "NTL Strat" and that is in one of your responsible teams, it will try and grab ANY team that contains "NTL Strat" including the ones that end in -Planning, -PM, etc. So as long as every team is separated by that dash or some sort of elaboration it should be fine.

I do foresee potential issues with the teams such as OS that are simple enough that a team like "ACROSS" would also be grabbed because the team contains "OS" in the larger word. I'm sure there is a more robust formula you could right to get it to be an exact match but hopefully this one fulfills your needs. You can see that in RESPONSIBLE TEAMS having Team 1 and the Email Recipients having Team 10 as well.

One last thing to note, is you should have the column types allow multiple contacts per cell so that it won't just pull them in as text.

Hopefully this should all work for you @kendallmar!

kendallmar

Hi @Dan Beres Thanks again! The formula is not working for me in the cells that have multiple teams selected. It's only working and pulling in the contact names when 1 team is selected. See picture below for reference:

I also do have the column types selected as Contact List but it is still only pulling in the raw text as opposed to the contacts. Maybe it is because I have them pulling in from a different sheet?? Not sure if you have any work arounds or updates/corrections that I should make to my formula to have this work.

As a reference here is the formula I have in all 5 columns the only change is the responsible, accountable, support, etc. teams @row reference.

=JOIN(COLLECT({Email Recipients for RASCI - Contact Range}, {Email Recipients for RASCI - Team Range}, CONTAINS([RESPONSIBLE TEAMS]@row, @cell)), " ")

Dan Beres

I noticed the same thing after I went back to my test sheet, that. If I swapped the column type to Text/Number column and then swapped it back to a Contact type column with multiple contacts selected it worked but only temporarily - it would go back to just plain text if I left and came back to the sheet. I have had this work with sheets before that maintain the text-typing, so I am not sure if because it is a formula it is having issues recognizing them as contact emails. The best way is to replace the " " (space) at the end of the formula with CHAR(10) is more consistent.

I am looking for another more straightforward solution for the multiple teams selected in the Team rows aside from having many nested formulas, because the only way I am getting it to work to track with multiple teams selected is about 40 nested formulas for a maximum of 5 teams being selected and then it breaks after the 6th team.

I would be curious if anyone else in the community would have some insight on multi-select dropdowns and parsing those formulas for index/collect searches. I'll keep searching and let you know if I stumble upon anything else!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.