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

 

[Fluff]

How about

ZFluff.xlsm
	A	B	C	D	E	F	G	H
1	Branch	Name	A/P/S/I	Class	PRI	M/F	Date app't to level	March 31/19 salary
2
3
4	IBSDB	Bonnie
5	SCI	Amy
6	PPB	George
7	AEB	Michael
8	PPB	Fred
9	Ops	Tom
10	Ops	Carol
11	AEB	Paul
12	PPB	Monica
13	PresO	Price
14	PPB	Chantal
15	AEB	Monique
16	IAB	Pierre
17	Ops	Denis
18
19
20
Data

ZFluff.xlsm
	A	B	C	D	E	F	G	H
1	Audit and Evaluation
2	2019-2020
3
4	Name	A/AI/P/R	Level	Rating	March 31/2020 Salary	% of Max	Salary Increase	April 1/2020 Salary
5	Michael	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
6	Paul	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
7	Monique	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
8
9
10
11
12
Sheet1

Cell Formulas
Range	Formula
A5:A10	A5	=IFERROR(INDEX(Data!$B$4:$B$17,AGGREGATE(15,6,(ROW(Data!$B$4:$B$17)-ROW(Data!$B$4)+1)/(Data!$A$4:$A$17="AEB"),ROWS(A$2:A2))),"")

Also please update your profile to show which version(s) of Excel you are using.

 

[Glacombe]

what is the 15,6, represents?

 

[Fluff]

15 makes the aggregate function work like the small function & the 6 tells it to ignore errors.

 

[Glacombe]

This is the message I get, would it make a difference if the names are last name comma and first name

 

[Glacombe]

This is my formula

=' (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:A1))),"")

 

[Fluff]

The sheet names should be enclosed in single quotes (apostrophes) like
INDEX('List of Ex1-5'!$B$2:$B$126

 

[Glacombe]

Sorry it does not seem to work now I get this message

 

[Fluff]

What is the exact formula you are using & what language are you using?

 

[Glacombe]

Please ignore my previous error message, I got it to work and thank you for everything

 

[Fluff]

Glad you fixed it & thanks for the feedback