+ Reply to Thread
Results 1 to 9 of 9

Dynamically update workbook using other workbooks

  1. #1
    Registered User
    Join Date
    05-01-2013
    Location
    Alabama
    MS-Off Ver
    Excel 2010
    Posts
    8

    Dynamically update workbook using other workbooks

    My boss wants me to create a "Discrepancy Report Form" as well as a "Discrepancy Summary" sheet.

    The idea is that as we save each new form, the summary will update with particular information from the new forms. I have done this without problems when everything is in the same workbook, but its been a real headache to automatically link new workbooks.

    Caveats here are that he does not want me to use VBA as he is worried that if something were to happen to me no one will be able to back engineer what I've done. Up until a few months ago he was an "Excel-o-phobe" and strictly used Word tables for cataloging sundry items. Another caveat is that these two forms need to be separate workbooks - not new sheets in the same workbook. He wants to be able to simply open up the Summary and see specific detail generated by the forms.

    I believe this is like creating a dashboard but in a separate workbook.

    I feel like this is something that is done regularly, but I apparently I dont know the correct syntax to search.

    What I have done so far:

    Generate (using functions) the complete path where the forms are located and filename therein - this updates as new forms are saved to the directory.

    I can manually update my summary form, but I cant get it to do so automatically.

    Any help would be greatly appreciated!

    Thanks

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Dynamically update workbook using other workbooks

    Without VBA. Probably the easiest method is to leverage PowerQuery and PowerPivot, both are free add-in for Excel 2010.

    Caveat is that you need to create list of file names somewhere on dashboard workbook and add Data validation dropdown to a cell.

    Then name that cell using name manager (with workbook scope). You can then use it as parameter in PowerQuery's "M" function.

    It would help if you upload sample workbook(s) both the source workbook and dashboard workbook (using exact naming convention as actual file). Along with some sanitized data and expected output (either done manually or via formula).

    For succession planning; be it formula based, VBA based, PowerQuery/PowerPivot etc. It is always good idea to have documentation detailing business rules and tutorial on maintenance and up-keep.

  3. #3
    Registered User
    Join Date
    05-01-2013
    Location
    Alabama
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Dynamically update workbook using other workbooks

    Attached (I think!) are my TestDashboard and sample reports.

    The reports should be in a separate directory - I'm using \Reports.

    Thanks! I'll look into PowerQuery and PowerPivot.
    Attached Files Attached Files

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Dynamically update workbook using other workbooks

    Ok, give me couple of days to workout logic and custom M function to dynamically pull all files from folder. I just got dumped urgent project at work.

    One question. Will the table in each source workbook be named "Table1"?

  5. #5
    Registered User
    Join Date
    05-01-2013
    Location
    Alabama
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Dynamically update workbook using other workbooks

    I had not decided on a specific name for the tables, so yes, Table1 should suffice.

    Thanks for your help!

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Dynamically update workbook using other workbooks

    Here you go. File is attached. You don't need PowerPivot but will need PowerQuery.

    In Param sheet, change folder path as needed. This is the folder where source files are located.

    When you refresh data, table in ImportedData sheet will update.

    Custom M function: funcImportTable
    Please Login or Register  to view this content.
    Above is custom function used to pull Table data from each workbook.

    It is used in Reports query.

    Reports Query M:
    Please Login or Register  to view this content.
    Most of these are generated via GUI. Only few lines are edited. Don't have time to write full explanation right now. But play with the file and let me know if you have questions.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    05-01-2013
    Location
    Alabama
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Dynamically update workbook using other workbooks

    Messing with it now

    Thanks!

  8. #8
    Registered User
    Join Date
    05-01-2013
    Location
    Alabama
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Dynamically update workbook using other workbooks

    @CK76 - works great!

    Thank you for introducing me to Power Query!

  9. #9
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Dynamically update workbook using other workbooks

    You are welcome and thanks for the rep.
    I started playing with it last year and am loving it. Simplified lot of my dashboard and reporting.

+ 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. [SOLVED] Update multiple workbooks with data from one workbook
    By Kimpan in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 10-16-2016, 12:28 PM
  2. Replies: 1
    Last Post: 08-29-2015, 03:04 AM
  3. Linking Workbooks to update Master Workbook
    By RichTea88 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 02-05-2013, 08:17 AM
  4. How to update master workbook with links to other workbooks
    By Launchnet in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 01-09-2012, 06:16 AM
  5. Getting workbooks to update a central summary workbook
    By KeiranMac in forum Excel General
    Replies: 0
    Last Post: 09-10-2009, 04:42 AM
  6. Replies: 2
    Last Post: 05-22-2007, 07:15 AM
  7. Update master workbook from individual workbooks
    By Annabelle in forum Excel General
    Replies: 0
    Last Post: 02-16-2006, 02:10 PM

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