Pull Contact Info From Another Sheet Automatically
This way as staff changes I only need to update the market details sheet to update all my other sheets.
Best Answer
-
[Department] should be a cross sheet reference pointing back to the reference sheet to be able to create the match.
Answers
-
Where exactly in the tracking sheet will the contact data go, and what can we use to match on?
Basically we are going to end up using an INDEX/MATCH which is similar to the VLOOKUP but a lot better in a number of ways.
-
For example with the Dallas Lab column on the tracking sheet the request comes in and associates to a (1) facility and (2) department.
Based off those two parameters go and lookup on the market sheet based and pull the appropriate POC details to auto populate at the row level all the appropriate POC: CEO, CNO, Radiology Manager, etc.
-
Since you are wanting to match on two separate variables, you will actually end up needing an INDEX/COLLECT along the lines of:
=INDEX(COLLECT({Lookup Sheet Email Address Column}, {Lookup Sheet Facility Column}, @cell = Facility@row, {Lookup Sheet Department Column}, @cell = Department@row), 1)
-
This is what I did:
Market Details Range 1 = I referenced the whole Market Details sheet
Market Details Range 1 = I only referenced the column of the POC title
Department = this is on the tracking sheet already so I changed it to [Department]
Which part am I doing wrong that I get the UNPARSEABLE error message.
I know this is a pain. I really do appreciate your help. If I can figure this formula out it will work wonders for many of my SS's.
-
[Department] should be a cross sheet reference pointing back to the reference sheet to be able to create the match.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!