+ Reply to Thread
Results 1 to 8 of 8

VLOOKUP between 2 workbooks without having both open

  1. #1
    Registered User
    Join Date
    06-21-2013
    Location
    Copenhagen, Denmark
    MS-Off Ver
    Excel 2010
    Posts
    3

    VLOOKUP between 2 workbooks without having both open

    Hi.

    I have two books, with an identifier-variable, where some are alike. Book 1 contains a complete list, while book 2 is a working book, where rows are gradually entered - again with each rower uniquely identified with this identifier-variable.

    I have constructed, a VLOOKUP-function that in book 1, genreates a "yes-or-no" variable, indicating whether the ID is present in both work-books. I have also constructed it in a way, so that everytime i in book 2 enter a new row, it will automatically update this "yes-or-no"-variable in book 1.

    My problem, however, is for this function to work, I need to have both book 1 and 2 open at the sime time - otherwise i get this "#reference"-problem. Since both books are relatively large, merging them is not really an option.

    Is there anyway, I can work in one of the books without having both open? Obviously, if I want it to update the "yes-or-no"-column in book 1, I would need to open both, but isn't there some way that excel can just treat the column as "fixed" until it is automatically updated, when I open both books?

    Thanks very much in advance. I have uploaded the books - however, I have the danish excel version - if it isn't understandable i will make a whole new book.

    Regards Alexander
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: VLOOKUP between 2 workbooks without having both open

    Hi
    The only way I can think of is to open both books initially then copy paste the "Values" from the VLOOKUP Column into an adjacent column. You can then close Book 2 while you are working on Book 1. When you need to update Book 2 you re-do these steps.
    Hope this helps.
    Tony

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

    Re: VLOOKUP between 2 workbooks without having both open

    A vlookup should work on a closed file. So, as long as book2 has been updated and saved with trhe new data, even if it's closed, a vlookup() in book1 should work fine. If you are getting an error message, it may be because that number hasnt been entered?

    An alternative may be, in book1, to just reference the data in book 2 directly (=Book2!A2 etc) and then base the vlookup on that - if you get an error, then the number isnt in book2
    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 someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    06-21-2013
    Location
    Copenhagen, Denmark
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: VLOOKUP between 2 workbooks without having both open

    @ARGK

    Thank you for your suggestion, this was also my "second-best"-solution.

    @FDibbins

    Are you sure? I've just tried again and it still doesn't work with the closed book. I am referencing directly - when book 2 is closed (the working book, where rows are entered), the VLOOKUP matrix reference has the path
    'C:\Documents and Settings\B14945\Skrivebord\Book2.xlsx'!TabelLille[Nr]
    where i named the table "TabelLille" and the first row is "Nr". By the way, the error that I get isn't "#N/A" indicating that it doesn't find the ID - it is "#reference" indicating the function doesn't work.

    Thanks for your replies either way.

  5. #5
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,760

    Re: VLOOKUP between 2 workbooks without having both open

    Referencing to a table with table reference requires the workbook needs to be kept open. So change your reference to range/cell reference to get rid of this issue.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  6. #6
    Registered User
    Join Date
    06-21-2013
    Location
    Copenhagen, Denmark
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: VLOOKUP between 2 workbooks without having both open

    @Sixthsense

    That did the trick! Thank you very much. The reason I wen't with the table-reference, was that I wanted the VLOOKUP matrix-range to incorporate the fact, that I gradually enter new rows in the table. However, I just went ahead and used the biggest possible range of
    !$A$2:$A$1048576
    which did it. It takes a litte bit of time for the VLOOKUP to search through all the cells, however, this is clearly tolerable.

    Thank you all for your replies. Very much appreciated that you would spend your own time helping me - it really helps me out in my job.

  7. #7
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,760

    Re: VLOOKUP between 2 workbooks without having both open


  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
    51,801

    Re: VLOOKUP between 2 workbooks without having both open

    Happy to help and thanks for the feedback

+ 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