Looking for Inspiration and ideas !

[elastigirl]

Hope this is the right forum.

I'm looking for some inspiration and ideas

I currently have a situation where I have 27 different excel workbooks which all roll up into one overall view (it's for budget tracking)

Each workbook is managed by a different person and has a specific category, the rolled up view shows a summary of the calculations from each workbook broken by category.

Each workbook is locked to the user so they can't mess with functions and can only fill in the bits I need !

Technically it works fine, in reality it's not great ! The issues are as follows:

If there is a requirement for a new column/function/section/conditional formatting etc.. I need to open each workbook and do it 27 times !! this is not only time consuming but the chance of making an error somewhere along the way is high. I've been trying to figure a better way to do this. Ideally if I could store all the functions in the one place and just call them in the individual workbooks then that might work. then I just make the changes once and it changes across the board, I've looked at doing this using a VBA Add-in, however all the workbooks are stored in a sharepoint library and I can't get that to work unless they are all on the one pc !

I think I may be over complicating this? and am wondering if anyone has any ideas on what I can do, ? One list in sharepoint restricted views to the individuals won't work because they need an individual workbook.

any ideas gratefully accepted

 

[Joe4]

My answer is similar to what I posted here earlier today on another question: http://www.mrexcel.com/forum/excel-questions/979994-combining-multiple-data-sources.html

So, you may not be using the best tool for the job. A database program like Access usually is better for things like this.

Distributing changes with a split Access database is pretty easy. When you have a split database, you have one back-end database that houses all your data (and nothing else). Then the front-end is your interface into the data, and contains all Queries, Forms, Reports, VBA, etc. Typically, each user has their own front-end, to avoid any conflict with other users.

What I usually do it create a batch file or VBS Script and put it on user's desktop to run the database. What the script does is copy down a fresh copy of the front-end from some location on the network and places it on their C drive in a designated folder, and then it opens this copy to run. This makes it very easy to distribute updates. All you have to do is overwrite the current version that is copied off of the network with the new version.

 

[HackSlash]

Any repeated action that will be identical can be replicated using the macro recorder.
Record the customization on one workbook and save the macro. Then run that macro on each workbook that needs the same changes

https://support.office.com/en-us/ar...e01-b015-3ea70e64937b?ui=en-US&rs=en-US&ad=US

This will eliminate the user error and speed up your deployment.

 

[elastigirl]

Thanks, that's a good suggestion

 

[elastigirl]

thanks, unfortunately I have to use excel otherwise I would consider something like this