+ Reply to Thread
Results 1 to 3 of 3

Copying items across workbooks

  1. #1
    Registered User
    Join Date
    10-13-2012
    Location
    Wisconsin
    MS-Off Ver
    Excel 2003
    Posts
    2

    Copying items across workbooks

    Hello! I've seen a similar question in the forums, but being not Excel-savvy, I can't figure out how to apply the solutions to my particular problem. I have data across workbooks that I need to copy from multiple workbooks into one master one. Basically, I need to search many workbooks for matching emails, and for those emails that match, copy values from specific columns into the master workbook. I have attached a shortened, anonymized version of what exactly I am working with: 1.2.comments.xls, 1.3.comments.xls, participation.xls.

    So, specifically, I need to search column A of 1.2.comments.xls and 1.3.comments.xls for emails that match column A of participation.xls. For those emails that match, I want to copy column D of 1.2.comments.xls into column D of participation.xls, and column D of 1.3.comments.xls into column E of participation.xls.

    Please let me know if any clarification is needed.

    I need specific directions about how to go about applying any help you may give me, and also a bit about how I modify to meet other needs and the larger data file I actually have.

    My apologies if this goes somewhere else, I couldn't tell what specific type of thing (ie, macro, etc) I needed.

    Lastly, I need this to work in both excel 2003 and excel 2010.

    Thanks so much!!

  2. #2
    Registered User
    Join Date
    03-10-2012
    Location
    Charlotte, NC
    MS-Off Ver
    Excel 2003, Excel 2007, Excel 2010
    Posts
    3

    Re: Copying items across workbooks

    There are a few ways you can tackle this, the easiest way, I can think of, (not involoving macros and such) would be to utilize the vlookup function.

    First, check out the tutorial link (below) on using the vlookup function as it may help out in shedding some light around how to use it. I would also do some searches on the web to find out more about the function as it can be pretty intimidating at first if you've never used it.

    http://spreadsheets.about.com/od/exc...320vlookup.htm

    One neat thing about the Vlookup function is that it can be used across muliple worksheets or workbooks. Check out the example I did using your three workbooks -- 1.3.comments.xls1.2.comments.xlsparticipation.xls, along with some cleanup functions wrapped around vlookup to remove any of the #N/A results (non-matching data).

    One little note -- If the vlookup function cannot find the workbook (deleted, filename change, moved to a different location on computer, etc.) the results may not show up anymore. I usually advise highlighting the whole column(s), copying, and then pasting special (values) to prevent that. Also decreases the amount of space and speed the excel document utilizes. Also if there are duplicates in your workbooks, it can be a little tricky in utilizing vlookup.

    Hopefully that makes sense and was what you were looking for.

  3. #3
    Registered User
    Join Date
    10-13-2012
    Location
    Wisconsin
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Copying items across workbooks

    I see it works fine the way you made it, but when I try reapplying it, it fails and I cannot, for the life of me, make it work. I tried looking at the link you sent, which makes some sense, but not enough where I can get anything. This is what I have, and it will. not. work.

    =VLOOKUP(A2,'C:\Users\(username)\Desktop\SALON\chapter 1\section 1.2\[1.2.annotations.xls_grades.csv]Sheet1'!$A$1:$D$400,4,0)

    -of course, I have my actual username where it belongs, edited here for anonymity-

    The workbook I want to search is in another folder than the workbook I am putting the function in. I have expanded the selection to include the great number of values, but I think there's nothing else much changed. I don't care about it looking clean, the N/A value is useful for me. Any idea what to change from here?

    It says "excel cannot update one or more links in this workbook", but when I go to the links as it says, it is linked fine, and then the #N/A! switches to #REF!.

    I don't know why this needs to be so frustrating, it should be easy to search multiple values and copy the rows.

  4. #4
    Registered User
    Join Date
    10-13-2012
    Location
    Wisconsin
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Copying items across workbooks

    I seem to have gotten it to work! I think the problem with the above code is the file I was trying to reference, the output file from a script I was running, was a .csv file. I opened them and re-saved as .xls files and upon entering the amended code, was presented with a file explorer window to find the correct file to reference.

    THIS CODE SEEMS TO WORK:
    =VLOOKUP(A2,'C:\Users\(username)\Desktop\SALON\chapter 1\section 1.4\[1.4.annotations.xls_grades.xls]1.4.annotations.xls_grades'!$A$1:$D$500,4,0)

    Thanks jmstampe for your help. I am now changing this to "solved".

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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