Results 1 to 3 of 3
Thread: Single vs Multiple Workbooks
2013-03-28, 17:35 #1
- Join Date
- Jan 2001
- Chicago, Illinois, USA
- Thanked 0 Times in 0 Posts
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.
2013-03-28, 18:23 #2
- Join Date
- Aug 2010
- Pa, USA
- Thanked 678 Times in 616 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 18:29.
The Following User Says Thank You to Maudibe For This Useful Post:
2013-03-28, 20:19 #3
- Join Date
- Mar 2004
- Manning, South Carolina
- Thanked 1,480 Times in 1,346 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
The Following User Says Thank You to RetiredGeek For This Useful Post: