+ Reply to Thread
Results 1 to 4 of 4

Excel - copy data to another sheet once criterias met

  1. #1
    Registered User
    Join Date
    01-13-2015
    Location
    Hong Kong
    MS-Off Ver
    word outloot excel
    Posts
    4

    Excel - copy data to another sheet once criterias met

    Hi All,

    I am looking for HELP.

    I have a excel table to store delivery job. we put Location, trucking number, delivery and service..etc in the sheet called (delivery)

    and it is required to copy some cells to sheet called (report) when it is match certain criteria on the sheet (delivery)

    example:

    if (delivery) A2 = HongKong and delivery date in C2 within 1 jan to 15 Jan . when the criterias matched it should copy to sheet (report). i try below formular but it is wrong.

    =IFERROR(INDEX(delivery!A$2:A$10,SMALL(IF(AND(delivery!A$2:A$10=$F$2,AND(delivery!$C3>=report!$D$2,delivery!$C3<=report!$E$2)),ROW(delivery!A$2:A$10)-1),ROW(delivery!A2))),"")

    F2=Location. e.g HongKong, Kowloon, NT D2= date_open E2=date_close

    attached two files:
    the TT-tracker-1 is the practice file but fail
    the second file MI tracker -2 is what i attended to achieve. But I doníít how to copy data and automatically sort out the blank row as like this file. Is there anyone know?

    Thank you in advance.

  2. #2
    Registered User
    Join Date
    01-13-2015
    Location
    Hong Kong
    MS-Off Ver
    word outloot excel
    Posts
    4

    Re: Excel - copy data to another sheet once criterias met

    resend with attachment

    MI Tracker-2.xlsmTT tracker-1.xlsx

  3. #3
    Registered User
    Join Date
    01-13-2015
    Location
    Hong Kong
    MS-Off Ver
    word outloot excel
    Posts
    4

    Re: Excel - copy data to another sheet once criterias met

    is there any tips for which part of the formula was going wrong?

  4. #4
    Registered User
    Join Date
    01-13-2015
    Location
    Hong Kong
    MS-Off Ver
    word outloot excel
    Posts
    4

    Re: Excel - copy data to another sheet once criterias met

    Hi All,

    not sure what was going wrong when the formulea simplified as below, the answer is correct.

    =IF(delivery!$A$2:$A$10=$F$2,"yes","no")

    but when putting one more condition in the if condition, it was wrong

    =IF(AND(delivery!$A$2:$A$10=$F$2,AND(delivery!$C5>=report!$D$2,delivery!$C5<=report!$E$2)),"yes","no")

    would be happy if anyone would advise some tips to solve the problem.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Userform to find specfic data with certain criterias and then copy to another workbook
    By guyinahatgames in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-21-2014, 09:32 AM
  2. Trying to input data from one sheet based on two criterias on another
    By danikva in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-03-2013, 07:39 PM
  3. [SOLVED] Delete rows based on multiple criterias, copy/paste data and formulas
    By BQuek in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-24-2013, 01:36 PM
  4. [SOLVED] Macro for copying data from one column in sheet 2 based on two criterias in sheet 1
    By baardings in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-13-2012, 01:50 PM
  5. Replies: 14
    Last Post: 07-12-2012, 01:32 AM

Tags for this Thread

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