Multiple cross references for one sheet?
Hi, here's the situation. Sheet A is a master list of employees with their title, supervisor etc. Sheet B has a column that is set up as a dropdown list of names of said employees. When I choose an employee on Sheet B I have it cross reference with Sheet A to pull the supervisor and some other information to fill in corresponding fields on Sheet B. This currently requires me to manually update the dropdown list of employees on Sheet B. Is there anyway to make it so that if I update the master list on Sheet A it will automatically also update the list of available employees on Sheet B and still pull the supervisor etc like it is currently set up to do? The column on Sheet B does not need to be dropdown if a different option will provide the result I'm looking for.
Answers
-
-
Hi,
I hope you're well and safe!
A solution that would be perfect for this use-case is the premium add-on, Data Shuttle.
Is that an option?
More info:
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅ Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Aweseome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!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.
-
Could an option be to have a number column in sheet A with a unique number for each row. You could the use INDEX/COLLECT to bring over the required information to Sheet B if you have the same reference column. Any new additions on Sheet A should be visible in Sheet B.
Sheet A Set Up:-
Sheet B Set Up:-
Formula in [Bring Employee Name from Sheet A] column. Note: column formula, referencing another sheet.
=IFERROR(INDEX(COLLECT({Sheet A - Employee}, {Number}, Number@row), 1), " ")
Formula in [Bring Supervisor from Sheet A] column. Note: column formula, referencing another sheet.
=IFERROR(INDEX(COLLECT({Sheet A - Supervisor}, {Number}, Number@row), 1), " ")
Formula in [Bring Title from Sheet A] column. Note: column formula, referencing another sheet.
=IFERROR(INDEX(COLLECT({Sheet A - Title}, {Number}, Number@row), 1), " ")
With it being a column formula you can have blank rows in both Sheet A and Sheet B (e.g. 11-23 below)… new additions in sheet A will appear in Sheet B
Sheet A Test:-
Sheet B Test:-
-
I will try both solutions and let you both know which worked. Data Shuttle is an option I just haven't used it yet. Thank you both so much - have a great day!
-
No problem, best of luck and I hope you get a solution that works for you :-)
-
Excellent!
Happy to help!
✅ Remember! Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Aweseome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 458 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 137 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
