+ Reply to Thread
Results 1 to 4 of 4

Linking a table across 2 workbooks, both ways

  1. #1
    Registered User
    Join Date
    05-27-2012
    Location
    Perth
    MS-Off Ver
    Excel 2007
    Posts
    2

    Question Linking a table across 2 workbooks, both ways

    Hello there

    I'm wondering if anyone can help me with this. I've got quite a bit of Excel and programming experience, but this is an issue I can't seem to figure out.

    I have to keep records of what invoices are sent to particular people and when they are received back from them. There will be one person processing the sending of the invoices and another person will be processing when the invoices are received back. To avoid having one workbook open by two people, I have decided to split the table of data into two workbooks.

    As such:
    I have two workbooks. Workbook one we shall call the 'Sending' workbook and workbook two the 'Receiving' workbook.

    Sending:
    Contains a table with headings relating to information about the invoices (irrelevant in this question), and a 'Date sent' heading. As well as a 'Date Received back' heading which is linked from the receiving workbook (we'll get to this one shortly)

    Receiving:
    Contains a table where headings relating to invoice information and the 'Date Sent' heading are linked from the sending workbook, so as all the data entered into the sending workbook is displayed in the receiving workbook (the user cannot edit this data in the receiving workbook). The receiving workbook also contains the 'Date received back' heading, which the user can input data into. This heading is also linked back to the sending workbook so that data entered into the receiving workbook is displayed in the sending workbook.

    This sort of almost creates a circle. 'Sending' creating the record and 'Receiving' adding to it.

    I have set this up and it works. BUT my problem is, is if you sort the data in one workbook (such as sorting A to Z), it screws things up. If you put a date received onto one invoice and then do some kind of data sort, that date received is now applied to a different invoice, like as if they are two separate tables and I am only sorting one. Also, sorting the table in one workbook automatically sorts it in the other workbook, which is also a problem.

    So I want to know if I can somehow get these tables to sort correctly. Or, if there is some kind of way to have a workbook open by multiple people, then that solution would be even better.

    I hope this hasn't been too confusing :-P Any help would be great! :-D

  2. #2
    Forum Contributor
    Join Date
    10-07-2011
    Location
    Plano, TX USA
    MS-Off Ver
    Excel 2013
    Posts
    141

    Re: Linking a table across 2 workbooks, both ways

    You might try using an unique identifier for each record, such as ID#. Then, you can sort by that ID# in each workbook and use VLOOKUP to find values.

    Jim

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Linking a table across 2 workbooks, both ways

    have you tried to link the 2 workbooks based on a vlookup() to each other, rather than (from the sounds of it) a direct reference? that way, no matter which file you sort, the other will still look for the reference into the other file
    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
    05-27-2012
    Location
    Perth
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Linking a table across 2 workbooks, both ways

    Thankyou thankyou! It works brilliantly. I can't believe I didn't think of that earlier :-)

    Thanks heaps for your help!

+ 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