+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    04-07-2013
    Location
    Lahore
    MS-Off Ver
    Excel 2007
    Posts
    9

    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
    Last edited by Mian USman; 04-14-2013 at 01:20 AM.

  2. #2
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

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

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Registered User
    Join Date
    04-07-2013
    Location
    Lahore
    MS-Off Ver
    Excel 2007
    Posts
    9

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

  4. #4
    Valued Forum Contributor wenqq3's Avatar
    Join Date
    04-01-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    868

    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.
    Attached Files Attached Files
    Last edited by wenqq3; 04-12-2013 at 04:33 AM.
    -If the problem is solved, please mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved".

    -Always upload a workbook before start your question
    To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

    +++ If my answer(s) helped you, please add me reputation by click on * +++

  5. #5
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

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

  6. #6
    Registered User
    Join Date
    04-07-2013
    Location
    Lahore
    MS-Off Ver
    Excel 2007
    Posts
    9

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

    It worked.

    Thanks a lot Fotis

    I really appreciate your effort

    Thanks again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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