Populate Contact List Column with Value from Another Table With Date Condition

pamela.moss89851

I want to populate cells in a Contact List column that is used in workflow automations based on a formula that pulls the contact(s) from another sheet.

Sheet fields: Resource(s) (type Contact List that allows multiple contacts per cell)

Start Date (type Date)

End Date (type Date)

Position (drop down - single select)

My other sheet tracks procurement requests. I would like to create a column formula that pulls in the appropriate resource into a contact field where:

the position matches a given string ("Budget Officer")

and the system Created date is equal to or between the start and end dates.

Only one row will match both the string and date conditions.

Ben Goldblatt

Hi @S-Jacob,

It may require multiple MATCH arguments or possibly incorporating the COLLECT function with an INDEX formula to populate the desired results. I've come across a few specific examples in the Community, such as in this post here: https://community.smartsheet.com/discussion/68963/index-collect, that may be helpful.

Ben

Ben Goldblatt

Hi @pamela.moss89851,

It is definitely possible to use cross-sheet formulas, maybe using VLOOKUP or INDEX/MATCH functions, to pull in contact values as results in a Contact List column but our Support Team has confirmed that there's currently a Bug in the system where contact formula results populate as text values initially and will only switch to actual contacts when the destination sheet is refreshed. This can be problematic if the goal is to use those populated contacts as recipients in Automation workflows.

We were not provided with a timeframe for when this issue will be resolved, but our teams are actively investigating and working to roll out a fix as quickly as possible. I'll update the thread here if I hear of any updates on this but, in the meantime, you may want to look into some other trigger action to take place after the sheet is refreshed to see if this will help. The refresh would still need to take place in order for the emails to send out to the appropriate people and it would require some manual work beyond the refresh to meet the trigger criteria.

Thanks,

Ben

pamela.moss89851

Hi Ben,

Thank you for informing me of the issue. I will pursue a different method to accomplish this task for now. I look forward to hearing it is resolved.

Warm regards,

Pamela

S-Jacob

@Ben G How would one use an INDEX/MATCH for this though?

What is the syntax for the MATCH function for A=B and C>=D and C<=F (Resource = Position AND Created equal to or greater than Start AND Created equal to or less than End)?

Thank you.

Ben Goldblatt

Hi @S-Jacob,

It may require multiple MATCH arguments or possibly incorporating the COLLECT function with an INDEX formula to populate the desired results. I've come across a few specific examples in the Community, such as in this post here: https://community.smartsheet.com/discussion/68963/index-collect, that may be helpful.

Ben

S-Jacob

Thank you very much, @Ben G . That looks to accomplish what I wanted.

pamela.moss89851

Thank you to @S-Jacob for clarifying the question to obtain an answer and to @Ben G for providing the answer. Once Smartsheet fixes the contact list issue, the formula can be implemented.

Matthew Henrich

Is there any update on this fix? I can import the list from another sheet using a column formula, but they don't appear on the destination sheet as contacts, just text, even when Contact List is the column property set.

Andrée Starå

Hi @Matthew Henrich

I hope you're well and safe!

Make sure that the columns match exactly (Single, Multiple).

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)

Did that work/help?

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 support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

compartido.tactic

Does anyone knows anuthing about the bug @Ben Goldblatt taked about las year:

"It is definitely possible to use cross-sheet formulas, maybe using VLOOKUP or INDEX/MATCH functions, to pull in contact values as results in a Contact List column but our Support Team has confirmed that there's currently a Bug in the system where contact formula results populate as text values initially and will only switch to actual contacts when the destination sheet is refreshed."

Has it being solved?

Thanks in advance

Genevieve P.

Hi @compartido.tactic

The bug that Ben is referring to has been resolved, yes. That issue was in relation to pulling a contact cell from one sheet into another, versus joining multiple contacts together through a formula (which is not possible at this time).

I see that you created an Idea post in the Product Feature Ideas category, thank you!

Cheers,

Genevieve