Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Coon Rapids, Mn
    Posts
    199
    Thanks
    22
    Thanked 4 Times in 4 Posts

    Pulling data from 11 workbooks to summary workbook

    I've been tasked with creating, or updating - modifying, an old process which I think was created with Excel 2007. We now use Excel 2013. I'd appreciate some advice as Excel is not really my forte though I do understand it at a basic level and have used spreadsheets for many years, just not with this degree of complexity. I don't know beans about VBA and am not a programmer by any means, I'm just trying to find a way to make this work. Having been here for a very long time, I am sure someone knows exactly how this can be done - this IS where the experts live. :^)

    The situation is we have data entered into workbooks containing 13 worksheets, one for each month and a total that sums the other 12. Then we have a summary workbook that should pull data automatically from the other 11 workbooks and so summarize function activity. I found the original workbooks, last used in 2010, and they used a formula that brought data in, but that seems cumbersome and in trying to recreate it, I've run into a couple, few issues. I'll attach copies of one of the workbooks, the summary workbook and the formula used.

    I am finding that the auto update feature does not work, I've established connections with the workbooks, but each time I open the universal workbook, I get this screen that says we can't update some of the links in your workbook, though automatic updating is selected, it makes me navigate to the actual file for each workbook - the workbooks, all of them are store on a LAN and my copy of Excel is on my actual desktop pc. Could that be part of the problem? But that is far from the only issue. If I click update values, I get a popup that again makes me navigate, one by one, to each workbook. Tedious.

    The formula used to total the workbooks is in the attached spreadsheet called input data workbook - just an example of one of the data workbooks. The second issue is the summary workbook, called instate Universal Team Totals. That formula is specific to each field for every workbook in every cell, manually entering those is also tedious, but it does work, even if every time I open the summary workbook I have to update values and reestablish the connections. That formula is, by way of example as follows:

    =SUM('T:\Child Support\EA_CSD_Supervisor\Report\Instate Enforcement\Other Instate Enf Reports\Instate Enforcement Case Management Results\2015 Reports\.xlsx versions\[Jacobson 2015 - Instate Enf Case Mgmt Results.xlsx]TOTALS'!$B$4)

    =SUM('T:\Child Support\EA_CSD_Supervisor\Report\Instate Enforcement\Other Instate Enf Reports\Instate Enforcement Case Management Results\2015 Reports\.xlsx versions\[Jacobson 2015 - Instate Enf Case Mgmt Results.xlsx]TOTALS'!$b$5)

    =SUM('T:\Child Support\EA_CSD_Supervisor\Report\Instate Enforcement\Other Instate Enf Reports\Instate Enforcement Case Management Results\2015 Reports\.xlsx versions\[Jacobson 2015 - Instate Enf Case Mgmt Results.xlsx]TOTALS'!$b$6)

    So, what I'm looking for is a better way to connect the 11 data workbooks to the summary workbook, so that it will automatically update without having to redo the connections each time I open it for it to update. Believe me, I've been reading and watching for a good bit of time trying to get this thing right. I appreciate any suggestions/ideas you might have. :^) gene
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Would like to help gene but I cannot make any sense out the Instate totals worksheet. Format and data sources seems too inconsistent and not clear. I have no problem under standing the Input data sheet but the other one has me stumped! Can you offer any explanation of the workflow of this sheet?

    Maud

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    I have attached a zipfile containing a Team Totals processing file, and a folder containing 10 demo test data files.

    Rather than use 'file links' in cells, the method I have used is to import 'snapshots' of the latest [Totals] sheet in each of the Team Members data files. This direct import method uses vba.
    Formulas on each of the month sheets in the Team Totals processing file grab the relevant data from the corresponding import sheet for each Team Member.
    I have used my current favourite colours in the Tool so you don't confuse it with your 'real' one.
    Each of the import sheets MUST match the Team Member name as entered on the [Main] sheet.

    To test this, save the zip file and extract all the files to a folder of your choice.
    For further info, please ask.
    This is just an initial start to show a different method.
    There are no file links in these sample files.

    zeddy
    Attached Files Attached Files

  4. The Following User Says Thank You to zeddy For This Useful Post:

    genej313 (2015-09-13)

  5. #4
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Coon Rapids, Mn
    Posts
    199
    Thanks
    22
    Thanked 4 Times in 4 Posts
    Quote Originally Posted by zeddy View Post
    Hi

    I have attached a zipfile containing a Team Totals processing file, and a folder containing 10 demo test data files.

    Rather than use 'file links' in cells, the method I have used is to import 'snapshots' of the latest [Totals] sheet in each of the Team Members data files. This direct import method uses vba.
    Formulas on each of the month sheets in the Team Totals processing file grab the relevant data from the corresponding import sheet for each Team Member.
    I have used my current favourite colours in the Tool so you don't confuse it with your 'real' one.
    Each of the import sheets MUST match the Team Member name as entered on the [Main] sheet.

    To test this, save the zip file and extract all the files to a folder of your choice.
    For further info, please ask.
    This is just an initial start to show a different method.
    There are no file links in these sample files.

    zeddy
    Thank you, I will look at these tomorrow with great interest. I think I've found another solution that works too through the data connection feature simply using an = function and clicking in the proper cell of the workbook that holds the data. But I'm very interested in the VBA possibilities too. :^)

  6. #5
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Coon Rapids, Mn
    Posts
    199
    Thanks
    22
    Thanked 4 Times in 4 Posts
    Quote Originally Posted by Maudibe View Post
    Would like to help gene but I cannot make any sense out the Instate totals worksheet. Format and data sources seems too inconsistent and not clear. I have no problem under standing the Input data sheet but the other one has me stumped! Can you offer any explanation of the workflow of this sheet?

    Maud
    Yes, the instate worksheet is actually one of 11 separate workbooks, each of which has a varying number of worksheets but all of which have a totals worksheet for each month of the year. They're on a LAN drive which is what makes them look so odd here, the address. As I mentioned in my post to Zeddy, I think I have a workable solution to my problem simply linking the appropriate cells from each workbook's totals sheets to the summary workbooks monthly totals. But I'm interest in visual basic greatly though I know little about it - my only real experience coding has been with html, so I am interested in expanding my understanding of coding, just for the pure fun of it. Thank you for your help! :^) gene

  7. #6
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    Genej313:

    A better way to tackle this project may be to segregate you links to be in their own area.

    So in the Summary Workbook, you should put in a place below and to the right of the Summary links all the summary monthly informaion being provided by each workbook. That way your links show all available data. Next, in the Summary Report put in formulas that reference the cell where the data is now shown seperately in the Summary Workbook.

    This allows you to use that information as needed by the Summary Report. Or any other future reports that may be needed. This way you keep things far more flexible.

    See the simple illistration attached.

    Hope this helps

    Regards,

    Tom D
    Attached Files Attached Files

  8. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Gene
    (had trouble with my spellchecker with that intro)

    ..the reason you may have had trouble with your auto-update as per your post#1:
    In your posted file [Instate Universal Team Totals 2015.xlsx] if you switch to sheet [Jan 2015].
    Now increase the 'depth' of your formula bar (i.e. to show very long cell contents) by using the mouse to drag the formula bar bottom-edge downwards.
    Now select cell [B2] and then cell [C2] (i.e. toggle backwards and forwards between these two cells).
    You will see that there is a subtle difference in the file path. In particular, there is an extra space in the last folder entry i.e. \.xlsx versions\.. and \.xlsx versions\..
    I suspect this could contribute to your auto-update issue.
    In effect, it means one of these paths probably doesn't exist, which is why you are required to 'locate the file'.
    Your 'solution' in post#4 suggests you have just replaced the incorrect cell formulas.

    zeddy
    Last edited by zeddy; 2015-09-15 at 09:42. Reason: typo

  9. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Gene

    ..your path-and-filename-length is also getting close to the Windows limit (the limit is ~240 chars I believe) after which problems will occur. You can reduce the path length by either mapping another drive to a deep-nested folder level e.g assign Q:\ to represent level
    T:\Child Support\EA_CSD_Supervisor\Report\Instate Enforcement\Other Instate Enf Reports\Instate Enforcement Case Management Results\
    ..or use UNC naming (e.g.. \\servername\Child Support\EA_CSD_Supervisor\Report\.. etc etc etc

    zeddy

  10. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Gene

    ..from my experience with issues related to 'linked files' I would recommend you avoid them whenever possible i.e. use vba to 'grab' any required data either on file-open or 'on-demand' or both.

    zeddy

  11. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    ..for anyone wishing to try the vba 'refresh' method as per post#3, the instructions are, after unzipping the entire contents of the posted zip file to a folder of your choice, open the tool and click the button to browse to the unzipped folder containing the sample files, then click the [Start] button to start the refresh process.

    zeddy

  12. #11
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Coon Rapids, Mn
    Posts
    199
    Thanks
    22
    Thanked 4 Times in 4 Posts
    Thanks to all for your advice and help. The path is long, but that is not something I can control, it is where it is on the lan, but something I found interesting as I looked through various things, including John Walkenbach's Excel 2013 Bible, is that creating the links is relatively easy with both source and destination document open. Click a cell in the destination file, type =, select a cell in the source file, which fills in, in the destination file, in the '!$B$4 format, but remove the $'s and the link is shortened and permanent, it also allows you to click and drag the formula down, or copy and paste - both of which I am using and Excel keeps the cell references straight in both documents. When you close the destination workbook, the link expands to the full path and when you change something in the source workbook and reopen the destination document, a simple update click, updates everything. Tested and tried many times. So I've been able to link all 11 workbooks, each of which has 12 monthly totals worksheets (that combine many other worksheets in that workbook), to a summary workbook without breaking any links. It is tedious to put together, I admit, so the idea of automating it hence with VBA is appealing and I will work on that for the 2016 versions. Again, thanks to all of you! :^) gene

  13. #12
    New Lounger
    Join Date
    Nov 2012
    Posts
    11
    Thanks
    0
    Thanked 2 Times in 2 Posts
    While you may not be able to control the file locations, you can as Zeddy suggests map a new drive letter to a network location closer to the end of the file path.

  14. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    ..if the source files were re-located to another folder (or drive letter), you would have to open all the source files again to redo all the link-formula cells again to point to the new file locations (or use Find-and-replace etc).
    Using the vba method as per post#3 you would just update the source-folder cell.
    The formulas in the post#3 file are also copied 'across-and-down'.

    zeddy

  15. #14
    5 Star Lounger Lugh's Avatar
    Join Date
    Jun 2010
    Location
    Indy
    Posts
    627
    Thanks
    168
    Thanked 77 Times in 68 Posts
    There is also the "Consolidation" tool, available in Excel 2013 in Data tab > Data Tools section.
    Quote Originally Posted by Excel help
    There are several ways to consolidate data:

    ## Consolidate by position
    Use this method when the data from multiple source areas is arranged in the same order and uses the same row and column labels. For example, when you have a series of expense worksheets that are created from the same template.

    ## Consolidate by category
    Use this method when the data from multiple source areas is arrange differently, but the same row and column labels are used. For example, you can use this method when you have a series of inventory worksheets for each month that use the same layout, but each worksheet contains different items or a different number of items.

    ## Use a formula to consolidate data
    Instead of using the Consolidate command (Data tab, Data Tools group), you can also consolidate data by using a formula.
    Quote Originally Posted by genej313 View Post
    The path is long, but that is not something I can control, it is where it is on the lan
    You can control it via the mapping Zeddy and CatHerder mentioned.

    Whenever I work on data which has external inputs [like LAN data], the first thing I do is grab a copy and put it in on my local drive. That eliminates variables like path changing and network performance. Fewer variables makes maintenance and troubleshooting easier.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •