Multiple cross references for one sheet?

VictoriaProctor

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.

BullandKhmer

https://ddupdate.com/

Andrée Starå

https://community.smartsheet.com/discussion/122552/multiple-cross-references-for-one-sheet

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: 

https://www.smartsheet.com/marketplace/premium-apps/data-uploader

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!

Protonsponge

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:-

VictoriaProctor

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!

https://community.smartsheet.com/discussion/comment/438080#Comment_438080

https://community.smartsheet.com/discussion/comment/438068#Comment_438068

Protonsponge

No problem, best of luck and I hope you get a solution that works for you :-)

Andrée Starå

https://community.smartsheet.com/discussion/comment/438092#Comment_438092

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!