Filtering from more than 1 sheets

[its_me_renan]

I have a series of worklist written in sheet 1 and sheet 2 as indicated below. What I want to happen is that at Sheet 3, I will have an input cell which is designated at A1 which when I type any word from column F of sheet 1 and 2, it will filter out that word and all information on the worlist can be seen at sheet 3. Example, I have a word "C-MS" at A1 of sheet3 then all worklist with C-MS will be filter out as indicated below. Thank you.

SHEET 1

	A	B	C	D	E	F	G
1	Item No	Notification	Description	Reported by	Planner group	Main work center	Created on
2	1	1001	condensate leak MSC line	Tony	Mechanical	C-MS	12/6/2018
3	2	1003	steam leak at 1003k column	Tony	Mechanical	C-MS	12/7/2018
4	3	1007	defective instrument	Tony	Instrument	IT	12/8/2018
5	4	1011	pump leak at casing	Fred	Mechanical	C-MS	12/9/2018
6	5	1015	loose bolts ant nuts	Ali	Mechanical	C-MS	12/10/2018
7	6	1019	swpping of compressors	Lee	Mechanical	C-MS	12/11/2018
8	7	1023	missing indicator	Ali	Instrument	IT	12/12/2018

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

SHEET 2

	A	B	C	D	E	F	G
1	Item No	Notification	Description	Reported by	Planner group	Main work center	Created on
2	1	1027	oil leak	Ali	Mechanical	C-MS	12/6/2018
3	2	1031	flange leak	Ali	Mechanical	C-MS	12/7/2018
4	3	1035	defective valve	Ali	Instrument	IT	12/8/2018
5	4	1039	detached bar	Ali	Mechanical	C-MS	12/9/2018
6	5	1043	loose thread	Ali	Mechanical	C-MS	12/10/2018

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

SHEET 3

	A	B	C	D	E	F	G
1	C-MS
2	1	1001	condensate leak MSC line	Tony	Mechanical	C-MS	12/6/2018
3	2	1003	steam leak at 1003k column	Tony	Mechanical	C-MS	12/7/2018
4	4	1011	pump leak at casing	Fred	Mechanical	C-MS	12/9/2018
5	5	1015	loose bolts ant nuts	Ali	Mechanical	C-MS	12/10/2018
6	6	1019	swpping of compressors	Lee	Mechanical	C-MS	12/11/2018
7	1	1027	oil leak	Ali	Mechanical	C-MS	12/6/2018
8	2	1031	flange leak	Ali	Mechanical	C-MS	12/7/2018
9	4	1039	detached bar	Ali	Mechanical	C-MS	12/9/2018
10	5	1043	loose thread	Ali	Mechanical	C-MS	12/10/2018

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[RasGhul]

Hi Renan,

I put data validation on sheet 3 A1 for C-MS & IT and the following filters between both sheets.

You will need to extend the ranges to suit your data, copy across then down how many total rows you'll need;

Book1
	A	B	C	D	E	F	G
1	Item No	Notification	Description	Reported by	Planner group	Main work center	Created on
2	1	1001	condensate leak MSC line	Tony	Mechanical	C-MS	12-06-18
3	2	1003	steam leak at 1003k column	Tony	Mechanical	C-MS	12-07-18
4	3	1007	defective instrument	Tony	Instrument	IT	12-08-18
5	4	1011	pump leak at casing	Fred	Mechanical	C-MS	12-09-18
6	5	1015	loose bolts ant nuts	Ali	Mechanical	C-MS	12-10-18
7	6	1019	swpping of compressors	Lee	Mechanical	C-MS	12-11-18
8	7	1023	missing indicator	Ali	Instrument	IT	12-12-18
Sheet1

Book1
	A	B	C	D	E	F	G
1	Item No	Notification	Description	Reported by	Planner group	Main work center	Created on
2	1	1027	oil leak	Ali	Mechanical	C-MS	12-06-18
3	2	1031	flange leak	Ali	Mechanical	C-MS	12-07-18
4	3	1035	defective valve	Ali	Instrument	IT	12-08-18
5	4	1039	detached bar	Ali	Mechanical	C-MS	12-09-18
6	5	1043	loose thread	Ali	Mechanical	C-MS	12-10-18
Sheet2

Book1
	A	B	C	D	E	F	G
1	C-MS		D Validation
2			C-MS
3			IT
4
5	Item No	Notification	Description	Reported by	Planner group	Main work center	Created on
6	1	1001	condensate leak MSC line	Tony	Mechanical	C-MS	12-06-18
7	2	1003	steam leak at 1003k column	Tony	Mechanical	C-MS	12-07-18
8	4	1011	pump leak at casing	Fred	Mechanical	C-MS	12-09-18
9	5	1015	loose bolts ant nuts	Ali	Mechanical	C-MS	12-10-18
10	6	1019	swpping of compressors	Lee	Mechanical	C-MS	12-11-18
11	1	1027	oil leak	Ali	Mechanical	C-MS	12-06-18
12	2	1031	flange leak	Ali	Mechanical	C-MS	12-07-18
13	4	1039	detached bar	Ali	Mechanical	C-MS	12-09-18
14	5	1043	loose thread	Ali	Mechanical	C-MS	12-10-18
Sheet3

Cell Formulas
Range	Formula
A6	{=IF(ROWS($A$6:A6)>COUNTIFS(Sheet1!$F$2:$F$8,Sheet3!$A$1)+COUNTIFS(Sheet2!$F$2:$F$6,Sheet3!$A$1),"",IFERROR(INDEX(Sheet1!A$2:A$8,SMALL(IF(Sheet1!$F$2:$F$8=Sheet3!$A$1,ROW(Sheet1!$F$2:$F$8)-ROW(Sheet1!$F$2)+1),ROWS($A$6:A6))),INDEX(Sheet2!A$2:A$8,SMALL(IF(Sheet2!$F$2:$F$8=Sheet3!$A$1,ROW(Sheet2!$F$2:$F$8)-ROW(Sheet2!$F$2)+1),ROWS($A$6:A6)-COUNTIF(Sheet1!$F$2:$F$8,Sheet3!$A$1)))))}
Press CTRL+SHIFT+ENTER to enter array formulas.