+ Reply to Thread
Results 1 to 3 of 3

Copy data from one worksheet to another worksheet based on criteria

  1. #1
    Registered User
    Join Date
    03-01-2015
    Location
    vasai
    MS-Off Ver
    2007
    Posts
    14

    Copy data from one worksheet to another worksheet based on criteria

    I am trying to track is when a Transaction Type is PO Receipt & PO Rcpt Adjust, that Transaction Type info is automatically pulled to separate sheet. The same thing for Transaction Type Intransit Receipt, Intransit Shipment & WIP component issue, that info will pull over to the another separate sheet. And for Transaction Type Miscellaneous receipt & Miscellaneous issue, that info will pull over to the another separate sheet.

    The same thing i have done manually in sheet Pur,Cons & Misc respectively.

    I am not sure if this can be done with a formula or if a macro is needed.

    The status on the Proposal Log tab will be manually updated by each estimator as the project award status is made known. I am just trying to have a semi-simple place to track this info.

    I am not sure if this can be done with a formula or if a macro is needed.
    Attached Files Attached Files
    Regards,

    Kunal Shah

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,739

    Re: Copy data from one worksheet to another worksheet based on criteria

    You can do this using 3 formulae and a little table. In the attached file I've inserted a new column A and set up a table in columns M and N of the main sheet, showing the transaction types and the sheet that you want them to be copied to. I've put this formula in A2:

    =IF(D2="","-",VLOOKUP(D2,M:N,2,0)&"_"&COUNTIF(A$1:A1,VLOOKUP(D2,M:N,2,0)&"*")+1)

    which identifies the sheet the record should be copied to and gives it a unique sequential number. The formula should be copied down beyond your data, to accommodate new data being added (I've copied to row 100, as can be seen by the hyphens).

    In the subsidiary sheets I've also inserted a new column A, and I've copied your data across to column M so you can compare the results of the formulae. In each sheet I have put the name of the sheet in cell A1 (also coloured with a yellow background) and this formula in A2:

    =IFERROR(MATCH(A$1&"_"&ROWS($1:1),'BG -1'!A:A,0),"-")

    This identifies the row in the main sheet where that record occurs. I have used this formula in B2:

    =IF(OR(A2="",A2="-"),"",INDEX('BG -1'!B:B,$A2))

    which is copied across to I2 to bring the relevant data from the main sheet. Then this row of formulae have been copied down (to row 40), to give you the results you are after. All the helper columns (column A - shown in blue) can be hidden if you want the sheets to look the same as before.

    Hope this helps.

    Pete
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    03-01-2015
    Location
    vasai
    MS-Off Ver
    2007
    Posts
    14

    Re: Copy data from one worksheet to another worksheet based on criteria

    Thanks Pete, It works absolutely perfect.

+ 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. Copy Data from one worksheet to another based on criteria VBA
    By craigg5 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-14-2015, 10:37 AM
  2. [SOLVED] Copy data from one row to another worksheet based on criteria
    By joeb23 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-27-2013, 02:16 AM
  3. [SOLVED] Copy rows from one worksheet to another worksheet based on a cell criteria.
    By keneco in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-07-2013, 10:28 AM
  4. VBA copy data to another worksheet based on criteria
    By wgaca07 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-29-2012, 07:51 PM
  5. Copy data from one worksheet to another one based two criteria
    By lagacey in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-07-2011, 12:12 PM
  6. Copy data from worksheet based on certain criteria
    By excelvb in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-08-2011, 12:34 PM
  7. Macro to copy worksheet data to another worksheet based on criteria
    By excelvb in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-07-2011, 04:11 AM

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