+ Reply to Thread
Results 1 to 8 of 8

Copy values from one sheet to another based on criteria

  1. 07-13-2018, 09:49 PM #1
    irose_57
    irose_57 is offline
    Registered User
    Join Date
    09-26-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Office 365
    Posts
    20

    Copy values from one sheet to another based on criteria

    Hi!

    I have a workbook (sample attached) that has two sheets. I want the second sheet to be based on values from the master sheet. In this case it would be columns A, B, C, F, and G but only if there is a value in column F.

    Not sure how to go about this.

    Thanks.

    Ian
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 07-13-2018, 10:48 PM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,933

    Re: Copy values from one sheet to another based on criteria

    Put this ARRAY formula in A2...
    =IFERROR(INDEX(Master!$A:$G,SMALL(IF(Master!$F$2:$F$11<>"",ROW(Master!$A$2:$A$11)),ROWS($A$1:A1)),MATCH(A$1,Master!$A$1:$G$1,0)),"")
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    Copy across, then copy all down as needed
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 07-13-2018, 11:44 PM #3
    irose_57
    irose_57 is offline
    Registered User
    Join Date
    09-26-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Office 365
    Posts
    20

    Re: Copy values from one sheet to another based on criteria

    Thanks!

    Exactly what I needed.

    Ian
    Reply With QuoteRegister To Reply
  4. 07-13-2018, 11:48 PM #4
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,933

    Re: Copy values from one sheet to another based on criteria

    Happy to help and thanks for the feedback
    Reply With QuoteRegister To Reply
  5. 07-14-2018, 01:01 AM #5
    irose_57
    irose_57 is offline
    Registered User
    Join Date
    09-26-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Office 365
    Posts
    20

    Re: Copy values from one sheet to another based on criteria

    Sorry but I still have a minor problem with this.

    If you look at Sheet1 then column D is not showing results from column AB of the master.

    Data in the spreadsheet is fictitious so no problems posting here.

    Ian
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 07-14-2018, 08:30 AM #6
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,933

    Re: Copy values from one sheet to another based on criteria

    Thats because the headings are not the same. Sheet1 D says Crew, but Master says Assigned crew. Make the headings the same and it will work
    Reply With QuoteRegister To Reply
  7. 07-14-2018, 06:00 PM #7
    irose_57
    irose_57 is offline
    Registered User
    Join Date
    09-26-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Office 365
    Posts
    20

    Re: Copy values from one sheet to another based on criteria

    And so it does!

    Thanks again - much appreciated.

    Ian
    Reply With QuoteRegister To Reply
  8. 07-14-2018, 10:45 PM #8
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,933

    Re: Copy values from one sheet to another based on criteria

    No problem
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Automatically copy row of data from Master Sheet to other sheet based on column criteria
    By Tiller332 in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 10-16-2017, 12:34 PM
  2. Automatically copy rows to another sheet based on criteria, when original sheet is updated
    By NoviceProgrammer in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-29-2016, 02:14 AM
  3. Copy data from one sheet to another sheet multiple times based on criteria from one sheet
    By manojuasc in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-23-2016, 01:16 PM
  4. Copy values from another sheet based on criteria
    By webisti in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-25-2013, 02:48 PM
  5. Copy and Paste Rows based on Criteria onto another sheet and sort based on oldest item
    By Kushal8684 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-25-2013, 05:37 AM
  6. [SOLVED] Copy and paste data from sheet 2 to sheet 1 based on specific criteria on sheet 1
    By VBADUD in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-05-2012, 04:18 AM
  7. Need Macro to search from mater sheet and copy in another sheet based on Criteria
    By humacdeep in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 02-29-2012, 01:03 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1