Populate Contact List Column with Value from Another Table With Date Condition
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.
Best Answer
-
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
Answers
-
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
-
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
-
@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.
-
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
-
Thank you very much, @Ben G . That looks to accomplish what I wanted.
-
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.
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 382 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 291 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!