Formula to list department name when individuals name is listed in another column
Hello!
Formula help for the following scenarios:
Scenario 1: When Jess Baker is selected in "stakeholder lead" column (multi select enabled), the "department" column automatically populates to "MEQA"
Scenario 2: When Dan Striker is selected in "stakeholder lead" column (multi select enabled), the "department" column automatically populates to "ME Ops"
Thanks!
Answers
-
Hi @sjohanson , This will depend on how many names you will be doing this with. If it is a reasonably small list of names, you could simply nest a bunch of IF() statements. Use HAS() when looking up the name in the column and then set the column to the department name that you want when the condition is met.
There are other options which could include creating a separate sheet that has the names in one column and the department in a second column. Then you could use cross sheet references using INDEX/MATCH. This won't immediately work with a multiselect contact column though because you don't know which of the names selected to match. If you can restrict your multiselect to just one name (single select), or if you can always reference only, say, the first name in the list, then you could do this. A lot to explain... so give this a go and write back if you need more instruction.
Good luck and be well!
If my response was helpful or answered your question please be sure to upvote it, mark it asawesome, or mark it as the accepted answer!
-
Hello Scott,
Appreciate the response! Can you write out what the IF() formula would be, if I decide to keep the list of names small?
-
Hi Scott - Following up on this.
-
@sjohanson
Scott gave good ideas on more scalable solutions but if the list is only Jess and Dan, you could do something like this:[Department]:
=IF(HAS([Multi Name]@row, "Matt Lynn"), "MEQA", IF(HAS([Multi Name]@row, "Sam Harwart"), "ME Ops", "No Department Found"))
You'll need to swap out Matt & Sam for Jess & Dan. This formula also doesn't account for what would happen if both Jess & Dan are selected, but I added a "No Department Found" if both are missing.
Thanks,
Sam
—
Want to chat about a Smartsheet problem you're facing? Grab time on my calendar here: Schedule a Discovery Call!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!