+ Reply to Thread
Results 1 to 8 of 8

Copy values from one sheet to another based on criteria

  1. #1
    Registered User
    Join Date
    09-26-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Excel 2007
    Posts
    16

    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

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    43,981

    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 some-1 helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    09-26-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Excel 2007
    Posts
    16

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

    Thanks!

    Exactly what I needed.

    Ian

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    43,981

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

    Happy to help and thanks for the feedback

  5. #5
    Registered User
    Join Date
    09-26-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Excel 2007
    Posts
    16

    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

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    43,981

    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

  7. #7
    Registered User
    Join Date
    09-26-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Excel 2007
    Posts
    16

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

    And so it does!

    Thanks again - much appreciated.

    Ian

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    43,981

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

    No problem

+ 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