+ Reply to Thread
Results 1 to 3 of 3

Two Workbooks - One Connected Sheet

  1. #1
    Registered User
    Join Date
    Vermont, USA
    MS-Off Ver
    Microsoft Office 365 Business

    Two Workbooks - One Connected Sheet

    Hi there!
    I have two workbooks with different purposes, each have a shipping sheet. I would like for the shipping sheet to be exactly the same in both workbooks and any update in either sheet, update the other workbook.
    Example below:
    Workbook 1:
    Shipping sheet:
    Adds shipment of shoes. Shoes cost 45.50

    Workbook 2:
    Shipping sheet:
    Shipment of shoes and cost are updated.
    And vice versa.

    I hope my objective is clear, if anyone can offer any guidance that would be appreciated.
    Thank you!

  2. #2
    Forum Expert sandy666's Avatar
    Join Date
    Any Country
    MS-Off Ver

    Cool Re: Two Workbooks - One Connected Sheet

    I think you'll need VBA if you want to overwrite data on each workbook
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  3. #3
    Forum Expert
    Join Date
    MS-Off Ver
    office 365

    Re: Two Workbooks - One Connected Sheet

    Regardless of controls in place the 2 sheets will go out of sync at some point
    - so keep things simple and run only one copy of the sheet
    - there are a several ways of achieving this and making it very easy for the user

    Suggestion1 - run 3 workbooks but make them behave like single workbook
    - clicking on sheet tabs in one workbook takes user to the correct tab in the other workbook
    - if the file is not open, it is opened automatically

    To test:
    - save the attached workbooks to the same folder
    - open "shipping.xlsm"
    - click on tab "File1"
    - user now in wbS1 (opens automatically)
    - click on tab "File2"
    - user now in wbS2 (opens automatically)
    - user now free to hop back and forth as if in a single workbook

    Example VBA from wbS2 - the other files contain almost identical VBA
    In ThisWorkBook module
    Please Login or Register  to view this content.
    In (sheet tab "shipping") sheet module
    Please Login or Register  to view this content.
    In (sheet tab "file1") sheet module
    Please Login or Register  to view this content.
    In standard module
    Please Login or Register  to view this content.

    Adapting it to your needs
    This is a staring point (and kept very basic) which could be modified in many ways to make it work well for you
    - eg "shipping" could be a sheet in either of the 2 workbooks and use the same trick as above
    If you like the suggestion
    - after you have had an opportunity to think about how it could work for you
    - come back and ask for more specific tailored help

    Other Uses
    I have 8 files that I use regularly throughout the day
    - each file is single sheet
    - they bear no relationship to each other but I am forever opening and closing them
    - so each of the workbooks has 7 additional tabs (named single character alpha)
    - tabs are always visible unlike a button
    - could get button to follow the cursor, but why bother?
    - did not want 8 different shortcuts
    - has low "system" overhead
    - results in easy access to my most used files
    Attached Files Attached Files
    Last edited by kev_; 04-14-2018 at 07:17 AM.
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

+ Reply to Thread

Thread Information

Users Browsing this Thread

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


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