+ Reply to Thread
Results 1 to 3 of 3

Two Workbooks - One Connected Sheet

  1. #1
    Registered User
    Join Date
    04-02-2018
    Location
    Vermont, USA
    MS-Off Ver
    Microsoft Office 365 Business
    Posts
    6

    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
    02-05-2015
    Location
    not-us
    MS-Off Ver
    PC: 5F / 61 / 7E0 (PQ/PP)
    Posts
    7,312

    Cool Re: Two Workbooks - One Connected Sheet

    I think you'll need VBA if you want to overwrite data on each workbook
    sandy (NO CAPS)
    Excel can do everything but still doesn't read user's wishful thinking
    The ready made solution will not teach anyone anything

    A logical description of the problem is the basis of dialogue and a rapid solution. Nobody sits in your head to understand your way of thinking
    Note: English is not my native language
    Keep It Sophisticatedly Simple

  3. #3
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    3,988

    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)

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