# How to transfer a row to another sheet when a criteria met

1. ## How to transfer a row to another sheet when a criteria met

Hi

I am working with a worksheet having data range from A1 to N10000.
I want to copy entire row from this sheet to second sheet automatically using a formula/function not VBA when a certain cell e.g. K100 in that row is blank.

Thanks  Register To Reply

2. ## Re: How to transfer a row to another sheet when a criteria met

So in A1 of your second sheet, something like this would work for you?

=IF(Sheet1!\$K\$100="",Sheet1!A\$1,"")

Copy across.  Register To Reply

3. ## Re: How to transfer a row to another sheet when a criteria met

Dear Fotis,

i am sorry that i could not make it clear.

i am attaching the sample file. Sheet 1 is the source sheet and i want the result on sheet 2 (as I mentioned) if J8 & J11 in sheet 1 is blank and so on. if i enter a number in J8 or J11 then in sheet 2 the row sould not appear.  Register To Reply

4. ## Re: How to copy an entire row from one sheet to another based on criteria

Attached file is correct too, but not so good.

New way:
=IF('1'!\$J2="",IF('1'!A2="","",'1'!A2),"")
I edit the formula,at sheet "2".
A2 =IF('1'!\$J2="",IF('1'!A2="","",'1'!A2),"")

then expend it to area you want.  Register To Reply

5. ## Re: How to transfer a row to another sheet when a criteria met

So try this ARRAY formula in A2.

=IFERROR(INDEX('1'!A\$2:A\$1000;SMALL(IF('1'!\$J\$2:\$J\$1000="";ROW('1'!A\$2:A\$1000)-1);ROW('1'!A1)));"")

As you have empty cells, formula gives you many zeroes as result. So i have a Conditional formatting rule for zeroes numbers..  Register To Reply

6. ## Re: How to transfer a row to another sheet when a criteria met

It worked.

Thanks a lot Fotis

Thanks again.  Register To Reply