+ Reply to Thread
Results 1 to 3 of 3

Combine workbooks or connect them to work together?

  1. #1
    Registered User
    Join Date
    10-24-2011
    Location
    Orlando, FL
    MS-Off Ver
    Office 365 for Business
    Posts
    95

    Combine workbooks or connect them to work together?

    Hello Everyone!
    I am looking for some general advice rather than anything specific. If needed for clarity, I can later send more data.
    I have 2 separate workbooks; Warehouse Inventory and Warehouse Suppliers.
    Warehouse Suppliers has multiple sheets(4) with each sheet containing a particular vendor's product data and pricing. Using Power Query, I merge these sheets into one Master Vendor table. All works great.

    WB Warehouse Inventory also uses Power Query to combine 4 city warehouse inventories into one Master Inventory list for the entire organization. All works great.

    My dilemma: The WB Warehouse Inventory needs to look up product data from WB Warehouse Suppliers.
    Should I move all of the worksheets from Warehouse Suppliers into Warehouse Inventory?
    Or, should I leave the WBs separate, and have Warehouse Inventory link to Warehouse Suppliers?

    Do I need to setup a Data Model for either of these options? I have had bad luck with Data Models in the past. Looking for the easiest options, both for myself and for my client.

    Thanks so much!

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Combine workbooks or connect them to work together?

    It depends on the scale of the whole thing... unless the individual files are already massive, I'd use ONE file for the lot and use formulae, rather than PQ, to combine everything. WithO365, it's easy tocombine data from different sheets especially if (and you didn't tell us this...) the individual sheets are formatted identically.

    If you supply some sample data (in a single file)... only 10-20 rows... with a mcok-up of how it sohould look, someone will leap in and offer working solutions.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU

  3. #3
    Forum Contributor frabulator's Avatar
    Join Date
    09-16-2015
    Location
    United States
    MS-Off Ver
    2019
    Posts
    105

    Re: Combine workbooks or connect them to work together?

    If the workbooks are already setup with tables for VLookup, XLookup or something along those lines, you could create a VBA sorting tool.

    There are several tutorials online on how to make your own VLookup workbooks linking multiple books together. Here is one from Microsoft.

    There are several third party applications out there for this kind of stuff. Three come to mind for me:
    EZ Vlookup (now part of CelTools) - Kutools - Ablebits

    None of those above are needed though. Excel is pretty much an open book with all the tutorials online
    Full time middle school art teacher, father and learner. Part time 'self taught' programmer.

    Helpful links:

    How to plot 3D Graphs in Excel - Free application for formatting Visual Basic strings - How to use VLookup methods

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Connect two workbooks
    By Eric Tsang in forum Excel General
    Replies: 1
    Last Post: 06-16-2023, 06:05 AM
  2. Connect of Workbooks
    By Eric Tsang in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-21-2022, 10:41 AM
  3. Help make my code work: Loop through folder of workbooks with multiple sheets and combine
    By carlito2002wgn in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-06-2019, 08:19 PM
  4. Connect multiple workbooks
    By nits1982 in forum Excel General
    Replies: 0
    Last Post: 08-02-2017, 12:34 PM
  5. need 6 workbooks to connect an change value of a certian cell in each workbook
    By Jenkins27 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-29-2015, 09:10 PM
  6. Replies: 6
    Last Post: 03-10-2014, 07:41 PM
  7. [SOLVED] combine multi workbooks into one master workbook but I want to combine only sheet 3
    By Goodstart14 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-31-2013, 12:22 AM

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