Workbook with lots of tabs and each tab needs access protection to view & edit

The only sure way to keep people from accessing other people’s data is not to have the other people’s data there. This can be accomplished if each person has his or her own workbook. The master workbook can then access these workbooks and import the data in. Basically you copy in the entire sheet the manager fills in into a duplicate page in the master workbook.

This process can be managed by a table that has a list of workbooks to be collected. As people come and go their workbooks can be added or deleted from the table.

Depending on the nature of the data, a better solution would be a database where each user gets a front end displaying data from a query that filters the data according to his/her login.

Other Excel solutions have limitations: most notably concurrency: multiple people trying to access the same workbook at the same time. Shared workbooks allow multiple people to make updates at the same time, but they don’t allow VBA code. Without VBA code, I can’t think of a way to hide worksheets selectively.

If concurrency is not an issue, I do have a solution and I can post it. I will have to tell you what you have to do to defeat the system so you can get access to the code so you can set yourself up as admin. (Or you can give me your login name. For example, mine is “dflak”).

Let us know which route you would like to take.

I think I see what you mean about the user making changes in the template. If they do it and it is in the same book, then the relative cell references of off-sheet formulas shift to keep up.

However, there are probably only certain key cells that you are going to want from each sheet. The attached spreadsheet outlines what you might want to do. The Shadow sheet has formulas in fixed positions that reference the Working sheet. You can add rows or columns on the working sheet and the formulas on the Shadow sheet will change to reference the new cell positions. If you look at the Shadow sheet, the key cells are still in a fixed position. so hide the shadow sheet from the user, but use the shadow sheet to gather data.

I realize this is an exceptionally simplistic model. However, it is a concept that could work depending on what you mean by changes to the template.