Copy rows from One worksheet of workbook to another Excell 2007

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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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)

Baxters801Author Commented:
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
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Baxters801Author Commented:
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???

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.


Baxters801Author Commented:
Thank you for all of your help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.