Hi. One of our Engineer's has created a spreadsheet with multiple worksheets and lots of egg-head calculations. Database is out of the question for now. Most are hidden and contain confidential data but one is open for user input. He's locked it down through excel but we need more security. We decided to split the user worksheet into it's own spreadsheet that links to a backend speadsheet on our server, which contains all of the data. We've used Windows file security to stop normal browsing to the backend speradsheet while still allowing the spreadsheets to talk (so to speak). The only problem we now face is that both spreadsheets have to be open to run the calculations. Here's two ideas we have and I'm looking for help in how to achieve one or both:
- Create a [CALC] button (using VBA) on the user spreadsheet that updates all fields and processes results. Button would need to open the backend (hopefully hidden in some manner), process the results, and close the backend spreadsheet immediately. Does this sound possible and how?
- Keep the backend spreadsheet open at all times on a machine in our server room and map the calcs so they read both the location of the file and the location of where the file is opened. Not sure if this is possible or how?
Hope that's enough for someone to help me on. Right now we are just testing with some basic calcs - actual calcs are much more involved:
Backend Spreadsheet = Test2
FrontEnd Spreadsheet = Test1
Files are attached: Again, Test2 is in a secure location and test 1 is open to users. I just want to run test1 without my users being able to see test2.
thanks:)
Start Free Trial
