Auto-populate workbook based off dropdown in another workbook

New Contributor

Hi there! 


I think there is an easy solution for this, but I cannot figure out what function to use. 

We use excel to track study recruitment progress for a research project. We have contact information to go off of for recruitment that is often times outdated and we make a list of all these cases so that we can try and find better information. Right now, we have one column in a spreadsheet that directly tracks the success of our contact (e.g., bad contact info, enrolled) and, in cases where we have bad information, we manually plug in the ID # of each person we have bad contact information for into a separate tab/worksheet. However, I am hoping that we can have that tab be auto-populated, so that when someone finds bad contact info and uses our data validation to mark it so, our separate tab/worksheet updates to include that persons ID #. I don't believe that xlookup or vlookup match this purpose, and no other functions come to mind that can pull in information in that way. 


It won't let me upload any example images but here it is in plain text: 

What would be in sheet 1 (with column B in a data validation drop down menu)

Sheet 1: 

A1 (ID)            B1 (status)

A2 (2)            B1 (enrolled)

A3 (3)            B1 (bad contact info)

A4 (4)            B1 (bad contact info)

A5 (5)            B1 (enrolled)


What would be in sheet 2 (based off of what is entered in column B)

Sheet 2: 

A1 (ID)

A2 (3)

A3 (4) 






3 Replies
best response confirmed by Hans Vogelaar (MVP)


The FILTER() Function is what you are loocking for.




A2 = FILTER('Sheet 1'!A2:A5,'Sheet 1'!B2:B5="bad contact information")




So you can mark my solution as: "best response"!

Thank you