[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 179
  • Last Modified:

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
0
Baxters801
Asked:
Baxters801
  • 3
  • 3
2 Solutions
 
pwustCommented:
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)

0
 
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
0
 
pwustCommented:
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.
vlookup.xlsx
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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???
0
 
pwustCommented:
Baxters801:

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.

e.g.,
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.

Regards,

Patric
0
 
Baxters801Author Commented:
Thank you for all of your help
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now