# 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

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.

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.

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.

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

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

It worked.

Thanks a lot Fotis

Thanks again.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1