Copy rows from One worksheet of workbook to another Excell 2007

Posted on 2011-10-11
Last Modified: 2012-05-12
Is there a way that I can copy rows from one table to another where the data from the copied rows is different then the data from destination sheet. There is one field,ID, which is the same across all worksheets
Question by:Baxters801
    LVL 8

    Expert Comment

    you could use vlookup:
    in destination workbook sheet's destination row: =VLOOKUP(link-to-ID-row-in-dest-sheet,Range-in-source-sheet-with-ID-as-first-row,number-of-relative-row-in-source-that-should-be-copied,FALSE)


    Author Comment

    Not sure that will work. I have about 10 spreadsheets where I need one or two columns in each of the spreadsheets copied into the destination spreadsheet which will have all of the copied columns as its own column
    LVL 8

    Accepted Solution

    I have attached a quite simple example, how you can consolidate the contents of 5 different sheets into one destination, using a common key row.
    Please note that in sheet "source3", the key row is not the leftmost row.

    Author Comment

    Thank you very much, your solution is working for the most part. It is not picking up all of the values from the source files...Any ideas???
    LVL 8

    Assisted Solution


    Without detail, i regret to have only general ideas.

    As a matter of fact, only IDs are searched in other sheets that are listed in the consolidation sheet, so this could be a reason.

    Another problem might be that the references that you use in the consolidation sheet are either pointing to a wrong source, or you are messing up between relative and absolute ranges.

    relative address = "source1!A5"
    absolute address = "source1!$A$5" or "source1!$A5" or "source1!A$5"

    This becomes important when copying formula with such reference to other rows:
    if cell B5 as copying source contains references like: "A5", "$A$5", "$A5", "A$5"
    as soon as copied to cell B6 (i.e. one cell down), the copied references will change to "A6", "$A$5", "$A6", "A$5"
    as soon as copied from B5 to C5 (i.e. one cell to the right), the copied references will change to "B5", "$A$5", "$A5", "B$5"

    If this doesn't help to resolve the issue, you may need to post some example to this question.



    Author Closing Comment

    Thank you for all of your help

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now