Indirect lookup - formula solution needed

[colinheslop1984]

I am using Excel 2016.

I would like to do a lookup function that also involved the indirect function.

I have multiple worksheets which belong to a coach and are titled accordingly - 'Peter' 'Sally' 'Chris' etc.
On my active worksheet I have a table that looks like below: (assume the below example starts at cell A1)

Coach	First Name	Last Name	Phone Number	Referred for jobs (Y/N)	Times Contacted
Peter	Claire	Jones		Y
Sally	Mike	Jones		N

Each coach's worksheets look identical to the example above (except for column A, 'Coach'), but all other cell headers are correctly placed, i.e. column B - first name, column C last name, etc.

Where column E (referred for jobs) says 'Y' on each coach's worksheet, I'd like that row to appear on my worksheet.

I was thinking I could use indirect to reference the coach's worksheet using column A (the coach's name) as the criteria to select the appropriate worksheet to performance the lookup. I would like to search column E for answer 'Y', which it would then return that row on my worksheet without any duplicates

I am hoping I have explained that well enough.

Can anyone help with a solution?

 

[colinheslop1984]

This was one solution I was trying but doesn't appear to be working. Although this would display a lot of blank cells, I was prepared to just add a filter to remove them from view

=IF(INDIRECT("'"&A$2&"'!$e1")="Yes",INDIRECT("'"&A$2&"'!$b1"),"")