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
[TABLE="width: 511"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Item No[/TD]
[TD]Notification[/TD]
[TD]Description[/TD]
[TD]Reported by[/TD]
[TD]Planner group[/TD]
[TD]Main work center[/TD]
[TD]Created on[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]1[/TD]
[TD="align: right"]1001[/TD]
[TD]condensate leak MSC line[/TD]
[TD]Tony[/TD]
[TD]Mechanical[/TD]
[TD]C-MS[/TD]
[TD="align: right"]12/6/2018[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]2[/TD]
[TD="align: right"]1003[/TD]
[TD]steam leak at 1003k column[/TD]
[TD]Tony[/TD]
[TD]Mechanical[/TD]
[TD]C-MS[/TD]
[TD="align: right"]12/7/2018[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]3[/TD]
[TD="align: right"]1007[/TD]
[TD]defective instrument[/TD]
[TD]Tony[/TD]
[TD]Instrument[/TD]
[TD]IT[/TD]
[TD="align: right"]12/8/2018[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]4[/TD]
[TD="align: right"]1011[/TD]
[TD]pump leak at casing[/TD]
[TD]Fred[/TD]
[TD]Mechanical[/TD]
[TD]C-MS[/TD]
[TD="align: right"]12/9/2018[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]5[/TD]
[TD="align: right"]1015[/TD]
[TD]loose bolts ant nuts[/TD]
[TD]Ali[/TD]
[TD]Mechanical[/TD]
[TD]C-MS[/TD]
[TD="align: right"]12/10/2018[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]6[/TD]
[TD="align: right"]1019[/TD]
[TD]swpping of compressors[/TD]
[TD]Lee[/TD]
[TD]Mechanical[/TD]
[TD]C-MS[/TD]
[TD="align: right"]12/11/2018[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]7[/TD]
[TD="align: right"]1023[/TD]
[TD]missing indicator[/TD]
[TD]Ali[/TD]
[TD]Instrument[/TD]
[TD]IT[/TD]
[TD="align: right"]12/12/2018[/TD]
[/TR]
</tbody>[/TABLE]


SHEET 2
[TABLE="width: 511"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Item No[/TD]
[TD]Notification[/TD]
[TD]Description[/TD]
[TD]Reported by[/TD]
[TD]Planner group[/TD]
[TD]Main work center[/TD]
[TD]Created on[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]1[/TD]
[TD="align: right"]1027[/TD]
[TD]oil leak[/TD]
[TD]Ali[/TD]
[TD]Mechanical[/TD]
[TD]C-MS[/TD]
[TD="align: right"]12/6/2018[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]2[/TD]
[TD="align: right"]1031[/TD]
[TD]flange leak[/TD]
[TD]Ali[/TD]
[TD]Mechanical[/TD]
[TD]C-MS[/TD]
[TD="align: right"]12/7/2018[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]3[/TD]
[TD="align: right"]1035[/TD]
[TD]defective valve[/TD]
[TD]Ali[/TD]
[TD]Instrument[/TD]
[TD]IT[/TD]
[TD="align: right"]12/8/2018[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]4[/TD]
[TD="align: right"]1039[/TD]
[TD]detached bar[/TD]
[TD]Ali[/TD]
[TD]Mechanical[/TD]
[TD]C-MS[/TD]
[TD="align: right"]12/9/2018[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]5[/TD]
[TD="align: right"]1043[/TD]
[TD]loose thread[/TD]
[TD]Ali[/TD]
[TD]Mechanical[/TD]
[TD]C-MS[/TD]
[TD="align: right"]12/10/2018[/TD]
[/TR]
</tbody>[/TABLE]


SHEET 3

[TABLE="width: 406"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]C-MS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]1[/TD]
[TD="align: right"]1001[/TD]
[TD]condensate leak MSC line[/TD]
[TD]Tony[/TD]
[TD]Mechanical[/TD]
[TD]C-MS[/TD]
[TD="align: right"]12/6/2018[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]2[/TD]
[TD="align: right"]1003[/TD]
[TD]steam leak at 1003k column[/TD]
[TD]Tony[/TD]
[TD]Mechanical[/TD]
[TD]C-MS[/TD]
[TD="align: right"]12/7/2018[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]4[/TD]
[TD="align: right"]1011[/TD]
[TD]pump leak at casing[/TD]
[TD]Fred[/TD]
[TD]Mechanical[/TD]
[TD]C-MS[/TD]
[TD="align: right"]12/9/2018[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]5[/TD]
[TD="align: right"]1015[/TD]
[TD]loose bolts ant nuts[/TD]
[TD]Ali[/TD]
[TD]Mechanical[/TD]
[TD]C-MS[/TD]
[TD="align: right"]12/10/2018[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]6[/TD]
[TD="align: right"]1019[/TD]
[TD]swpping of compressors[/TD]
[TD]Lee[/TD]
[TD]Mechanical[/TD]
[TD]C-MS[/TD]
[TD="align: right"]12/11/2018[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]1[/TD]
[TD="align: right"]1027[/TD]
[TD]oil leak[/TD]
[TD]Ali[/TD]
[TD]Mechanical[/TD]
[TD]C-MS[/TD]
[TD="align: right"]12/6/2018[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]2[/TD]
[TD="align: right"]1031[/TD]
[TD]flange leak[/TD]
[TD]Ali[/TD]
[TD]Mechanical[/TD]
[TD]C-MS[/TD]
[TD="align: right"]12/7/2018[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]4[/TD]
[TD="align: right"]1039[/TD]
[TD]detached bar[/TD]
[TD]Ali[/TD]
[TD]Mechanical[/TD]
[TD]C-MS[/TD]
[TD="align: right"]12/9/2018[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]5[/TD]
[TD="align: right"]1043[/TD]
[TD]loose thread[/TD]
[TD]Ali[/TD]
[TD]Mechanical[/TD]
[TD]C-MS[/TD]
[TD="align: right"]12/10/2018[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.