Data in Sheet 1 to Sheet 2 if meets criteria in Sheet 1

New Contributor

This may be fairly straight forward, I'm hoping so! I have data in Sheet 1: Names in Column A, Program Code in Column B. I need Sheets 2 - 12 to take any Names from Sheet 1: Column A to populate into Sheet 2: Column A for each Program Code in Sheet 1: Column B. 

 

For example:

Sheet 1: Column A, Row 2 is "Anderson, Andy", Sheet 1: Column B, Row 2 is "LA1600"

Sheet 1: Column A, Row 3 is "Flintstone, Fred", Sheet 1: Column B, Row 3 is "LA2500"

Sheet 1: Colum A, Row 4 is "Mouse, Mickey", Sheet 1: Column B, Row 4 is "LA1600"

I need Sheet 2: Column A, Row 2 to display "Anderson, Andy" and Sheet 2: Column A, Row 3 to display "Mouse, Mickey" and any other names from Sheet 1: Column A that contain Sheet 1: Column B as "LA1600", but NOT "LA2500" or any other code. There are 11+ different codes Sheet 1:Column B could be. Sheet 1 will have names and Program Codes added and deleted, so I need them to populate automatically on Sheet 2 when one is added or deleted.

 

Thanks for any help!

3 Replies

@Jaxwustl 

If you have Microsoft 365 or Office 2021, you can use the FILTER function.

See the attached version.

@Hans Vogelaar Thanks for your help. I attempted the Formula but didn't work, just lots of dinging from the machine. I do have Office 365. I appreciate your help! I'll mess around with the Filter function more.

@Jaxwustl 

Does it work for you in the sample workbook? I had to convert the tables on the second and following sheets to ordinary ranges.