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:

<tbody>
</tbody>

NO	DATE	BRAND	ORDER	MODEL	QTY
1	21/8/2019	CATHAY <tbody> </tbody>	C123456 <tbody> </tbody>	AIRBUS <tbody> </tbody>	200
2	21/8/2019	SILKAIR	SA123467	BOEING	500
3	23/8/2019	GARUDA	G125768	AIRBUS	450
4	24/8/2019	KOREAN	K984721	BOEING	300
5	26/8/2019	SIA	S239435	AIRBUS	200
6	26/8/2019	BRITISH	B340789	AIRBUS	1000
7	27/8/2019	JAL	J432576	BOEING	300
8	28/8/2019	KOREAN	K984234	BOEING	500
9	29/8/2019	KOREAN	K984231	BOEING	300
10	30/8/2019	QATAR	Q764123	AIRBUS	600
11

<tbody>
</tbody>

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