Populate cell based on Y or N choice from another tab

[mmalta]

Hi all,
I have this worksheet that tracks trip/meeting attendees. Some choose to use a transfer car some don't. There are two tabs, Attendees and Transfers. If on the Attendees tab, the answer on column F is "Y" for a Guest, then the name of the Guest should populate on the Transfers tab column B, if not, then don't bring the guest name over. I tried a nested if statement but it is duplicating names on the transfers tab since it is testing the same row in multiple cells when pulling the formula down. Any help to make this work would be much appreciated.

 

[ETAF]

try
=FILTER(Attendees!$C$13:$C$32,Attendees!$F$13:$F$32="Y")
if you have a version of excel which supports the filter function

you could use sort as well
=SORT(FILTER(Attendees!$C$13:$C$32,Attendees!$F$13:$F$32="Y"))

 

[bosco_yip]

If you are an Excel beginner, try to use helper column way as below

1] In "helper" A13, formula copied down:

=IF(Attendees!F13="y",MAX(Transfers!A$12:A12)+1,"")

2] In "Guest" C13, formula copied down:

=IFERROR(INDEX(Attendees!C$13:C$32,MATCH(ROW(A1),Transfers!A$13:A$32,0)),"")

 

[mmalta]

Hi,
Thank you for the tip! My version of Excel does not support those functions, but I'm certainly saving this to use in the near future.

 

[mmalta]

Hi Excel Ninja,

This works beautifully. I got stuck when trying to figure out how to use a helper column.

Thank you so much!

 

[bosco_yip]

Hi Excel Ninja,

This works beautifully. I got stuck when trying to figure out how to use a helper column.

Thank you so much!

You are welcome

Regards