Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Chicago, Illinois, USA
    Thanked 0 Times in 0 Posts

    Question Single vs Multiple Workbooks

    I'm building an application that really should be in Access (but that is NOT an option). Is it better (or what pitfalls are there) to split the project up into two or three separate workbooks that pass information into a summary / analysis workbook versus into stuffing everything into a single (large - multi-MB) workbook? Management wants a dashboard and production wants to mine minutiae.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Pa, USA
    Thanked 769 Times in 701 Posts

    Just one point of view

    Databases are ideal: compact, and versitile. But for one reason or another, there are times we are forced to make spreadsheets mimic them. And that takes a fair amount of effort to do so.

    I have created projects both ways. Setting up a master file with multiple subordinates maintans smaller file sizes, speed, and better orginization with each workbook serving a different purpose, for example, data collection and storage. Following the workflow of a project was simplied when acknowleging that each workbook has a specific function, like departments of a company. If a need arises to rewrite one of the workbooks, you are not forced into dealing with the entire project. Writng VBA code was less cluttered with fewer worksheets, modules, and userforms to weed through. However, the maintenence was time consuming. Files could not be arbitrarily moves, modifications involved multiple workbooks to be open, troubleshooting issues in one workbook often spanned to others, errors would exist if they became asynchronous requiring additional code to keep them in synch, and linking the data was tedious with long wordy functions and routines. These drawbacks may be kept to a tolerable level, but if there is more than one person involved, it could be a nightmare. I also found that once built as multiple workbooks, it was more difficult to merge them into a single file as oppose to splitting a single file to multiple ones.

    On the other hand, having a single file has a lot of clutter when writing code but if you keep your modules organized and employ intuitive labeling it is doable. Everything is at hand and flipping between sheets and modules was far less arduous that flipping between workbooks, sheets, and modules. Making sense out of the shorter functions and routines was less difficult while transferring data between sheets was so much easier than between workbooks especially when password protection was in use. The one file can be dragged anywhere, even on a thumb drive and while worksheets within a workbook may still have to be synchronized, it was far less work to do so. With multiple files, a user may be updating one component while another user updates another. While there are methods to prevent this, the skill and attention needed is more demanding.

    From my experience, it is much easier to have one workbook. And like a company, it is frequent that one department has no idea what the others departments do.
    Last edited by Maudibe; 2013-03-28 at 17:29.

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    Zeno (2013-04-01)

  4. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Manning, South Carolina
    Thanked 1,605 Times in 1,449 Posts

    I built a project like this years ago just before I retired. They put me on contract for 6 years after I retired to maintain it.
    It actually worked very well for the situation. It was a training organization with several divisions. The data collected was the same across divisions just the courses were different. This worked out well because it didn't limit usage to one person at a time. Each division could have a person inputting data at the same time. It kept data separated by division so there wasn't any worrying about what someone else was doing and you couldn't blame input errors on someone outside of your immediate organization. When upper management wanted their reports they ran a master workbook that copied the data from each of the divisional workbooks and then could do reports at that level. Each time the top level workbook was updated it was a simple wipe of the data sheet and then serially loading each of the divisional data sheets one below the other, no complicated update/synchronization problems here! It was also very easy to add/delete divisional units when reorganizations happened which was pretty frequent in this organization. It was pretty much a win-win all around. It also let the division managers manipulate their data any way they wanted to as long as they didn't change the master (data) sheet layout. Another nice feature was that the input menus could have different logic where appropriate w/o affecting the other divisions and at the same time keeping the logic for any one input menu much simpler. HTH
    May the Forces of good computing be with you!


    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Zeno (2013-04-01)

Posting Permissions

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