Proprietary Info and Caching of External Data

[Doubledjunky]

I've read over these threads for quite a few years now and have almost always found any answers I needed, or at least got pointed in the right direction. Unfortunately, I need to ask for your expertise.

I am using Excel 2007. I put together an Estimate sheet that pulls date from a Master sheet using vlookup. The lookup value is the same for all formulas on the Estimate sheet.

Everything is working perfectly, except for one thing. It seems like Excel is caching the data. The master list is all propietary information, and as such, I don't want it going anywhere. I've done the following steps so far:

The file is only able to be opened in read-only without a password.
The file is saved so that it is blank until you open it.
Then it refreshes all data and can be used as intended.

The problem is if the file is opened and saved elsewhere or emailed, it seems like Excel is caching the data and the worksheet is still useable even outside of the network (the master sheet is located on the company server in the same folder as the original estimate sheet). If you change the lookup value, all other fields are being updated as well, even though the Master and Estimate are no longer on the same network.

I am linking worksheets and not using a data connection to avoid a copy of the master sitting on the sheet. I tried the data connection first, but had the same problem with data staying on the table when emailed.

I was wondering if there is a way to stop the caching or if there is another way to address my problem that I haven't thought of yet.

And if you need any more info to be able to help, please let me know and I will provide what I can. I've been trying to find a fix for a few weeks now, and exhausted all of my current knowledge.

 

[Jerry Sullivan]

Hi and Welcome to MrExcel,

That's a good observation and most Excel users are unaware of this risk to confidential information.

This post by Bill Jelen (MrExcel) explains why that happens. In his example, the Vlookup is just referencing a range by its address, however the same result would occur from referencing a named range in an external workbook.

http://www.mrexcel.com/forum/excel-...ire-table-stored-inside-linking-workbook.html

I don't completely understand how you want the workbook to function for recipients; however it would be best that your formulas not include references to lookup ranges that include confidential information, even if the result of those formulas is not returning values from the confidential cells.

 

[Fazza]

I don't completely understand how you want the workbook to function for recipients

Same for me, and the reason I didn't reply earlier.

What about sending out a pdf copy of the file?

 

[Doubledjunky]

Thanks for the info on that. And thank you both for the responses. I'll try to clarify what my end goal is now. Maybe there is a work around that I have not thought of. And I apologize in advance for the lengthy post. When I get started, it's hard to stop the babble, lol.

We have 2 seperate woorkbooks, a Master list and an Estimate sheet. For company purposes, the Master will not be accessible. The Estimate sheet will be accessed through our intranet and can only be opened in Read-Only without the password. This part works beautifully.

Our main concern is this info getting to our competitors. The real Master is a compilation of over 30 years of company experience and time. As it is currently, if the Estimate sheet is opened from the intranet and emailed (or saved to USB or cloud storage) the cache allows the Estimate sheet to still pull all of the info from the cache. This means if an employee sent the file to a competitor for any reason, they would be able to compile everything needed in less than a day. This would be less than ideal for the company, understandably. The original goal was to make this Estimate sheet non-functional outside of our company network.

Since Excel caches the entire range, this obviously is an unattainable goal with the current method. Do you know of any work arounds for this? Or a way to convert the spreadsheet to an interactive intranet page or form? Then the Estmate would be accessible only on our intranet and not outside of our network, goal accomplished.


Sample of a simplified Estimate sheet: Fields in black are using vlookup to pull from Master file. Yellow field is the lookup value.

Sampl of Master (with falsified info):

 

[Doubledjunky]

I'm still woking on this, and have made some slight headway:

I'm using a VBA macro to block the "Save As" function.

I have a password on the file, so it can only be opened in read-only (and cannot "Save"). This way I am still able to open the file with the pasword and save normally when I need to updated the file as opposed to blocking both Save and Save As using the same macro.

I am currently looking into customizing the office ribbon to disable the "Send" button (so the file cannot be sent as an attachment).

This may accomplish what I am trying to do, but was windering if anyone out there had any other ideas/work arounds that you have done or heard of to restrict sharing of the file. Anything possible less complex that can be maintained / modified by a less experienced Excel user than myself.

 

[Jerry Sullivan]

Are you only concerned with an unintentional release of the proprietary data, or do you have the need to prevent access from an internal user that might intentionally take the data?

The methods you're considering are better suited to stop the unintended release. To stop someone with a little bit of knowledge from getting the data you would probably need to implement a system in which the user doesn't have direct access to the data. That's along the lines of the idea you noted of having an "interactive intranet page or form". Even with that type of system there should be a proxy in-between the user and the proprietary data that limits the queries that can be made of the master database. I don't have any expertise in this area, but what you describe is a pretty common need for online businesses that expose a portion of their data to the public.

 

[Doubledjunky]

Yes, it's to stop in unintentional release of info. While I doubt any of our current employees would purposely steal it and share it with a competitor, there's always that chance. I think doing it this way is one of my 2 choices.

The other option I am exploting is using SpreadsheetConverter to turn the excel sheet into calculating webpage, which will hide all the data and formulas.

I know each method will have it's strengths and weaknesses, I think the main concern is making unintentional theft a non-concern, and making it so that anyone trying will have a hard enough time that they won't even try. At the moment, none of the employees have the knowledge or intuition to save the webpage or hack it and pull the data.

Thanks for all the help and especially for confirming the cache issue for me. I thought I was losing it at first.

Thanks All,
David

 

[shyy]

When you send the spreadsheet out, can't you hard code that sheet so nothing gets pulled and you are only dealing with hardcoded data?

 

[Doubledjunky]

By hardcode, do you mean convert everything to values?

If so, that would not work. Out sales staff needs to be able to open the Estimate file and change C31 (yellow above). The file needs to then pull new data from the master file and give correct pricing for that building. All the fields in black above would change once a new building number is entered in C31.

 

[shyy]

or how about creating macro where if the cell is changed, to insert formula. After said change, to hard code info. This way everytime you change C31, the file will be up to date.