copy the values of the cell based on multi choice drop down menu

OshaK

I'm curious if what I want to do is possible. I have two sheets: 1. contains all the data for the admin assistants. 2. contains all the data for the faculty.

In sheet 1, each admin usually supports multiple faculty members (but it could be just one). So, I have a multi-choice drop-down with the faculty names in it.

In sheet 2, each faculty member only has one admin assigned to their.

Is it possible based on the drop-down selection in sheet 1 to auto-populate the corresponding columns (Admin Name, email, etc) in sheet 2, so they can only be updated once in sheet 1 (for example, if AA leaves and their faculty are assigned to a new AA, this can be updated in sheet2?

Sheet 1

Sheet 2

Paul Newcome

Are you able to provide screenshots with sample data for reference so we can see which data pieces are flowing to where?

OshaK

thank you. @Paul Newcome yes.

so from sheet 1 (Admin sheet)

each admin assistant is assigned faculty member(s) - could be one or multiple (multi choice drop down menu)

in sheet 2, depending on the drop down selection(s) in sheet 1, I need the data to populate from sheet 1 for additional columns - AA Name, AA Phone Number, AA Email etc. One faculty has always one AA assigned from sheet 1, but one AA can have multiple faculty members from sheet 2.

Ideally, I also would love to also reverse populate data from sheet 2 to sheet 1 for Faculty Names - from the text/number type of column to a multiple choice drop-down. but that would be the secondary task.

thank you

Paul Newcome

Give this a try:

=INDEX(COLLECT({AA Phone #}, {Faculty}, HAS(@cell, [Faculty Name]@row)), 1)