Reference cells from source worksheet to destination if condition are met

[FaezMH]

Good day,

I have the following sample data(excerpt from a large dataset) below and name it as Sheet1:

[TABLE="width: 543"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD]NO[/TD]
[TD]DATE[/TD]
[TD]BRAND[/TD]
[TD]ORDER[/TD]
[TD]MODEL[/TD]
[TD]QTY[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]21/8/2019[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="class: xl66, width: 73"]CATHAY[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 105"]
<tbody>[TR]
[TD="class: xl66, width: 105"]C123456[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 138"]
<tbody>[TR]
[TD="class: xl66, width: 138"]AIRBUS[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]21/8/2019[/TD]
[TD]SILKAIR[/TD]
[TD]SA123467[/TD]
[TD]BOEING[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]23/8/2019[/TD]
[TD]GARUDA[/TD]
[TD]G125768[/TD]
[TD]AIRBUS[/TD]
[TD]450[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]24/8/2019[/TD]
[TD]KOREAN[/TD]
[TD]K984721[/TD]
[TD]BOEING[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]26/8/2019[/TD]
[TD]SIA[/TD]
[TD]S239435[/TD]
[TD]AIRBUS[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]26/8/2019[/TD]
[TD]BRITISH[/TD]
[TD]B340789[/TD]
[TD]AIRBUS[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]27/8/2019[/TD]
[TD]JAL[/TD]
[TD]J432576[/TD]
[TD]BOEING[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]28/8/2019[/TD]
[TD]KOREAN[/TD]
[TD]K984234[/TD]
[TD]BOEING[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]29/8/2019[/TD]
[TD]KOREAN[/TD]
[TD]K984231[/TD]
[TD]BOEING[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]30/8/2019[/TD]
[TD]QATAR[/TD]
[TD]Q764123[/TD]
[TD]AIRBUS[/TD]
[TD]600[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I would like to reference the cell to sheet2 and sheet3 if condition is met.

For sheet2, I am using the following formula: =IF(Sheet1!$C2<>"KOREAN",Sheet1!A2,IF(Sheet1!C2="KOREAN",Sheet3!A2,"CHECK AGAIN"))

* this means that if the condition is not "KOREAN", then the cell will be copy automatically. However, my problem is in between, I had no4, 8 and 9 showing "check again" status. My question is how can I "eliminate" this "check again"?

Same goes to sheet3 where I am using this formula: =IF(Sheet1!$C2="KOREAN",Sheet1!A2,"CHECK AGAIN").

I am sort of running of idea what to do with this. Is there a better way I can do this. I had like to have Sheet2 showing only non KOREAN result without "check again" status and Sheet3 showing only KOREAN result without "check again" status.

Thank you.
Faez

 

[Fluff]

Hi & welcome to MrExcel.
How about

Book1
	A	B	C	D	E	F
1	NO	DATE	BRAND	ORDER	MODEL	QTY
2	1	43698	CATHAY	C123456	AIRBUS	200
3	2	43698	SILKAIR	SA123467	BOEING	500
4	3	43700	GARUDA	G125768	AIRBUS	450
5	5	43703	SIA	S239435	AIRBUS	200
6	6	43703	BRITISH	B340789	AIRBUS	1000
7	7	43704	JAL	J432576	BOEING	300
8	10	43707	QATAR	Q764123	AIRBUS	600
9
10
11
Sheet2

Cell Formulas
Range	Formula
A2	=IFERROR(INDEX(Sheet1!A$2:A$11,AGGREGATE(15,6,(ROW(Sheet1!A$2:A$11)-ROW(Sheet1!A$2)+1)/(Sheet1!$C$2:$C$11<>"Korean"),ROWS($A$2:$A2))),"")

Copy formula down & across

 

[FaezMH]

Thanks Fluff. That is a perfect solution!

Appreciate that...

 

[Fluff]

You're welcome & thanks for the feedback