Excel 2007 : Shared Spreadsheet w/Multiple Tabs

[Amarilis Abreu]

Hi,

For some cheap and barbaric reasons, my company does the Fiscal Year Budget in Excel.

We have approximately 26 different department compressed into 10 divisions.

The way we are currently set up –for privacy reasons–, we have created different folder in a shared drive with restricted access to the right users and created 3 spreadsheets per folder (expenses, revenue and salaries).

A main spreadsheet under the ‘creator’ folder captures all this information in 33 tabs through vlookups and similar reference formulas.

As you can imagine, any change in codes, percentages, employee names, etc needs to be updated in many spreadsheets to maintain the integrity of the information. Overall this is a very flawed system and leaves room for multiple errors.

I tried sharing a spreadsheet and hiding tabs through VBA properties or simply hiding the columns with reference for each tab and protecting sheet, but there are 2 problems with this scenario:
1. If the person forgets to hide and protect these cells each time they go in, the other users can see their information.
2. If the creator wants to see all tabs, has to open, protect and unprotect each one each time.

Is there a better way to simplify these 34 spreadsheets into 1 without compromising the privacy?
Thank you!!
Amarilis

[boardwalktech]

don't feel like you're alone-- even microsoft collects budget inputs from departments using Excel. Boardwalktech has developed a database abstraction running on SQL Server which is driven entirely by Excel. You can eliminate the multiple tabs and partioned shared drives because our solution has row/column access control and automates the entire roll up process.you might want to check us out (we get a lot of queries as budget season rolls around)..

[Miraun]

Hi Amarilis;

Hate to say it, but with the requirements you seem to have, it may be better building an access database. There's a wonderfully PITA 'feature' in Excel where if you enable a worksheet for Sharing (ie: multiple people accessing it at once), it turns off the capacity to run VBA. You can integrate in passwords and access controls in an access database, and run a similar, table structured backend database.

Alternatively, would it be feasible to create VBA pulling scripts for each of the smaller spreadsheets, and a consolidation macro on the consolidated spreadsheet. This way the consolidated spreadsheet can function as the reference data, and can be repopulated from the 38 on a periodic basis, and the 38 can pull new, relevant data down from the consolidated on demand. I don't quite know the format of the data, or the overlap between departments... but those are the only two things that come to mind offhand.

[Amarilis Abreu]

Thanks so much for the access option. I can’t really justify why but I’m still afraid of leaving the comfort of excel behind and move to access. I worked in creating an access database many years ago and can certainly familiarize again with it but my fear is that the other users would be reluctant to change… In the meantime, I found this spreadsheet online and I think these VBAs would work but I have only 2 questions:
1. I intent to add about 30 tabs and about 10 passwords -the idea is for each director to have access to approximately 3 departments/tabs. How could I assign the same password to more than one worksheet?

2. I intend to share the spreadsheet and post in a network server so all directors can access and modify. Tried to set as shared, leave it open and open from another PC. However, when I opened it, all the other tabs are visible and shows as ‘Shared’ and ‘Read Only’. Can this be fixed?

Thanks!!!