List from another sheet criteria

[ShanonExcelQs]

Hi There

I please need help, I have tried myself with index and match but it is complicated.

I need on my report sheet, to list the modules(that are on the courses sheet) that match the grey block, which is a drop down list with the same info on the courses sheet (Stage Column).
So I need to see what modules are Outstanding, redo, ect.

Book1
	B
22
REPORT

Book1
	B
20
COURSES

 

[DeficientOptimism]

Can't see the data you've shared it's blank?

 

[ShanonExcelQs]

Apologies, please see below:

Book2
	A
1	OUTSTANDING
2	List of modules that have the above Stage in Column 6 on second sheet
3
4
5
6
7
8
REPORT

Cells with Data Validation
Cell	Allow	Criteria
A1	List	OUTSTANDING; REDO; IN PROGRESS SCRIPTING; IN PROGRESS VISUALS; APPROVAL REQUIRED; TO BE LAUNCHED; COMPLETED

Book2
	A	B	C	D	E	F	G	H
1	COURSE	MODULE	SOURCE EXPERT	ASSIGNED	RAW CONTENT	STAGE	COMMENTS	ETA
2	The Promotional Industry - You Are Here	Why promotional products work	Nukiwe	Nukiwe	ü	COMPLETED	Launched	Launched
3	The Promotional Industry - You Are Here	The Economic Enviroment - Micro	Nukiwe	Nukiwe	û	OUTSTANDING	Create content	March
4	The Promotional Industry - You Are Here	The Economic Enviroment - Macro	Nukiwe	Nukiwe	û	OUTSTANDING	Create content	March
5	The Promotional Industry - You Are Here	Managing End User Expectation	Nukiwe	Nukiwe	û	OUTSTANDING	Create content	Feb
6	The Promotional Industry - You Are Here	What's in the bag (Registration Pack)	Nukiwe	Nukiwe	ü	IN PROGRESS SCRIPTING	To be Redone	Feb
7	The Promotional Industry - You Are Here	Introduction to Amrod and the business evolution	Nukiwe	Nukiwe	ü	REDO	To be Redone	March
8	The Promotional Industry - You Are Here	Promo Industry in the Post COVID world.	Nukiwe	Nukiwe	û	OUTSTANDING	Create content	March
9	The Promotional Industry - You Are Here	Fabrics and Fabrications when selling clothing	Omer		ü	OUTSTANDING	Create content	March
10	The Promotional Industry - You Are Here	Tips for Success Maximising The Busy Promo Season	Nukiwe	Nukiwe	ü	COMPLETED	Content available and edited. Ready to lauch	Launched
11	Selling 101	Capitilize on Collaboration|Understanding your Cients Needs	ASI	Soraya	ü	COMPLETED	Launched	Launched
12	Selling 101	25 productivity hacks to get things done	ASI	Soraya	ü	COMPLETED	Launched	Launched
13	Selling 101	Fantastic Follow Ups: Convert Leads Into Sales & Customers Into Fans	ASI	Soraya	ü	COMPLETED	Launched	Launched
14	Selling 101	Whats Your Bounce Back Plan? 21 Strategies To Implement Today	ASI	Soraya	ü	COMPLETED	Launched	Launched
15	Selling 101	Selling to the 21st century	ASI	Soraya	ü	COMPLETED	Loaded on AA - Not published-awaiting draft mailer from Marketing	Launched
16	Selling 101	Building profitable client relationship	ASI	Soraya	ü	TO BE LAUNCHED	Loaded on AA - Not published-mailer to be approved	Feb
COURSE

Cells with Data Validation
Cell	Allow	Criteria
D2:D16	List	Soraya & Shanon, Soraya; Shanon; Nukiwe
E2:E16	List	ü; û
F2:F16	List	OUTSTANDING; REDO; IN PROGRESS SCRIPTING; IN PROGRESS VISUALS; APPROVAL REQUIRED; TO BE LAUNCHED; COMPLETED

 

[DanteAmor]

This is an array formula. Maybe @Fluff or @Eric W can help us with a formula for version 365.

Dante Amor
	A
1	OUTSTANDING
2	List of modules that have the above Stage in Column 6 on second sheet
3	The Economic Enviroment - Micro
4	The Economic Enviroment - Macro
5	Managing End User Expectation
6	Promo Industry in the Post COVID world.
7	Fabrics and Fabrications when selling clothing
REPORT

Cell Formulas
Range	Formula
A3:A7	A3	=IFERROR(INDEX(COURSE!$D$1:$D$16,SMALL(IF(COURSE!$H$1:$H$16=$A$1,ROW(COURSE!$H$1:$H$16)),ROWS($A$2:A2))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

A formula for xl365 & 2021

=FILTER(COURSE!B2:B100,COURSE!F2:F100=A1)

 

[ShanonExcelQs]

A formula for xl365 & 2021

=FILTER(COURSE!B2:B100,COURSE!F2:F100=A1)

this worked perfect thank you so much!

 

[alansidman]

An alternative is to use Power Query with a parameter to allow you to filter only the data you select for the Stage.

 

[Fluff]

Glad we could help & thanks for the feedback.