Creating a multi-sheet formula to match a contact list that references a drop-down list
Hello to you, Smartsheet community! I am in need of assistance with formulas. I'm so lost in the syntax that I don't know right from wrong, up from down, HAS or COLLECT 😵. I do not identify as a power user with formulas — wizards are my dearest friend — so this push into the advanced features has been a journey.
Is it possible for an INDEX column formula to return a contact list from one sheet to another, by matching two columns; one of which is a dropdown list? If so, how?
Images of relevant columns in the images attached. What we're attempting to accomplish is for the Company Contact Email contact list in the Submitted Food Products sheet to return the CC Email contact list from the Company Contacts sheet by matching the Food Company columns of both sheets. Our difficulty in accomplishing this comes from the Food Company column in Company Contacts being a dropdown list column type.
I attempted HAS and COLLECT in my INDEX formula, but my grasp of syntax and the language of what I actually want has been a total bust. We've been able to match our contact lists from those with single entries in Food Company, but not multiples. At best, we've run into the #NO MATCH or #UNPARSEABLE errors.
It is my hope the above explanation makes sense and a formula solution exists. We're wanting to make the data work for our contacts by making duplicates and other burdensome data entry steps automated.
Assistance, guidance, and solutions are more welcome. 🙇♀️🙇♂️
Justin Trautmann
Oregon Farm Direct | Nutrition and Health Screening | Center for Prevention & Health Promotion | Public Health Division | Oregon Health Authority
justin.c.trautmann(at)oha.oregon.gov
Best Answer
-
It is going to be something along the lines of
=INDEX(COLLECT({Email Address Column}, {Food Company Column}, HAS(@cell, [Food Company]@row)), 1)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
It is going to be something along the lines of
=INDEX(COLLECT({Email Address Column}, {Food Company Column}, HAS(@cell, [Food Company]@row)), 1)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Paul Newcome , it works! Your mastery of syntax and power over the parenthetical arts has saved us from duplicative data and a cleaner experience on this crazy information super highway. May this Friday bring you as much joy as you've brought to us. Thank you.
Justin Trautmann
Oregon Farm Direct | Nutrition and Health Screening | Center for Prevention & Health Promotion | Public Health Division | Oregon Health Authority
justin.c.trautmann(at)oha.oregon.gov
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!