locking one parameter in a pivot table

[miconian]

Every week I send a report out to a bunch of sales reps. The report is basically a pivot table that pulls from a data worksheet. Each rep gets the same pivot table setup, with a single parameter changed: their own name, of course.

The reps are not allowed to see each other's numbers. So in order to enforce that rule, each pivot table is "hardcoded" i.e. copied and paste-values back onto itself, thus eliminating the actual pivot table, and turning the report into just a bunch of cells in the shape of a pivot table.

But... I want the reps to have pivot tables. They each have an opinion about how they would like to arrange the data. I want to give them the ability to play around with it.

The problem is, of course, that if I send them actual pivot tables, they will also look at each other's data. Is there some way around that? Can I prevent them from changing one particular field, but have free reign over the rest of the table?

I realize that I could just give each one their own raw data only, but this is being automated, and I am not ready to tackle that problem yet.

Thanks!

 

[miconian]

I was able to set up a new query, even with the source sheet password-protected. Maybe I'm protecting it the wrong way (using the general protect options under "save as"), but it seems to me that if a clever enough person were to figure out how to create a new query, they could query someone else's name. No?

 

[miconian]

Hm... I thought perhaps it wasn't asking me for a password because I was already in a session. So I closed out of Excel, started it again, and opened the client workbook. When I tried to refresh the data remotely, I got an "External table is not in the expected format" error. So now I admit I don't know what's going on.

 

[SydneyGeek]

I think you're protecting the workbook, not the sheet. Protecting the workbook is useful because you can prevent users from adding and removing sheets.

To protect a sheet you go to Tools > Protection > Protect Sheet. You get a prompt for a password, and once you enter that you will see many of the icons inactivate.
In 2007 the Protect Sheet option is in the Review tab.
To unprotect the sheet you need to provide the password.

Note: Excel is NOT a secure work environment. You will stop the average user. A cluey person will get around any Excel security.

Denis

 

[miconian]

Protecting the sheet does not prevent someone from querying the data on the sheet to build a pivot table in another workbook. That's the type of protection that is needed here.

I understand that if the worksheet is protected, and someone finds it, then they can't view the data directly. Great. But they also need to be prevented from querying it.

I realize that we're not talking military-grade security here. But surely there is a way to make a query require a password?!

 

[miconian]

I guess I should add that the data source does not have to be an Excel workbook. It could be any type of file that can be created with either Excel 2007 or Access 2007. I am not very experienced with databases, but can learn.

And the users in question don't have access to Access (so to speak). So putting the source file in a form that can't be easily opened with Excel would probably be sufficient security for now anyway.

 

[miconian]

Seems like if I query 'from analysis services,' I get prompted for a username and password. But does Analysis Services mean building an OLAP cube? My understanding is that this can't be done from Excel 2007. Although I have Access 2007; maybe it can be done from there?

Obviously I'm fumbling around in the dark a bit here, but could use some help on which subjects to explore.

 

[miconian]

Hmmm... there is some information here about how to save a password with an Excel data query. But it's not clear on how to enter the password to begin with, or how to require its use.

 

[SydneyGeek]

Instead of loading the data into Excel, build the pivot table directly on the Access source. Then the users don't have the raw data to work with.
I had some code at home to allow you to filter the data for each user -- see if I can dig it out later.

Denis