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.

 

[Doubledjunky]

It would work as long as the pc was accepting macros. In the office, we can set that up, but if the file is sent off-site and macros are not enabled, that function would no longer work.

Even with that, excel would still have the cache, and you would still be able to hack the formula and display the entire range per the link that Jerry provided earlier. With macros enabled and the cell having only a value, the cache is still there. And the outside user could still go through the list of buildings at their leisure and piece together our entire database in less than a day. That is the main concern, I believe.

Thank you for the suggestion, though. My train of thought hadn't gone down that path previously.

 

[shyy]

Surprised Microsoft hasn't fixed this or provide a solution. That is pretty scary if you know the clients nomenclature and can still get info despite hard code data. I guess that explains files appear big size but with very little information displayed.

 

[ConorFoley]

Seven years later and I have exactly the same query - has anything new happened since then?

Would it help to password protect the workbook (so the data is not accessible to a hex editor), and then protect the sheet and hide all formulas etc - hide any rows / cols that don't need to be visible.

Would that work or are there still ways someone could access parts of the cached data that you don't want them to see?

Conor