Formula to insert names

[Glacombe]

Good afternoon, I have a worksheet that contains employees and their sectors, which we have 14 sectors, what would be the formula to extract all the names from one particular section and add them to another sheet within the workbook?

Thanks

 

[Glacombe]

Sorry one more thing, when I insert a row it opens File explorer for me to go get the spreadsheet where my data is and both sheets are in the same workbook

 

[Fluff]

That suggests the sheet name is not spelt correctly.

 

[Glacombe]

This is my workbook sheets

This is my formula

=IFERROR(INDEX('List of Ex1-5'!$B$2:$B$126,AGGREGATE(15,6,(ROW('List of Ex1-5'!$B$2:$B$126)-ROW('List of Ex1-5'!$B$2)+1)/('List of Ex1-5'!$A$2:$A$126="AEB"),ROWS(A$1:A3))),"")

And when I insert a row on my sheet, this is what opens up

 

[Glacombe]

Sorry, I believe I know where the error is, my sheet data is within a table format, could that be the reason

 

[Fluff]

On any sheet other than List of Ex1-5 in a blank cell put = then select the List of Ex1-5 sheet, click in A1 & press enter.
What formula do you get?

 

[Glacombe]

This is the formula I get

=' List of Ex1-5'!A2

 

[Glacombe]

sorry its this

=Table2[[#Headers],[Branch]]

 

[Fluff]

Oops forgot it was a table, do the same but this time select a cell outside of the table

 

[Glacombe]

My data sheet List of Ex1-5 is within a table

 

[Glacombe]

this the same but this time I selected a cell outside the table and this is the formula

=' List of Ex1-5'!A127