Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Protecting Formulas from a range from being viewed
Hi, I have a .xls with 15 worksheets.
It consists of 9 sheets containing Bill of Materials and pricing for an individual project. I need to hide a range of formulas on each sheet so that my client cannot view the formula (the formula contains sensitive pricing lookups) I am happy for the customer view the formula result in this range and to play around and see/edit anything else. I need to switch this on and off as I send out each version. Workbook protection is too severe and client cannot do anything. pls help! -- Brisbane, QLD Australia |
#2
|
|||
|
|||
You'll be far better off if you first turn the formulas into values, and
then save "this worksheet" into a new workbook, and send THAT instead of sending your "calculating" workbook (which you would close without saving). For instance, a macro like this could help you: http://vbaexpress.com/kb/getarticle.php?kb_id=377 ************ Anne Troy www.OfficeArticles.com "Andrew Heath" (delete) wrote in message ... Hi, I have a .xls with 15 worksheets. It consists of 9 sheets containing Bill of Materials and pricing for an individual project. I need to hide a range of formulas on each sheet so that my client cannot view the formula (the formula contains sensitive pricing lookups) I am happy for the customer view the formula result in this range and to play around and see/edit anything else. I need to switch this on and off as I send out each version. Workbook protection is too severe and client cannot do anything. pls help! -- Brisbane, QLD Australia |
#3
|
|||
|
|||
Anna
Thanks for your reply. The problem with that is that the formulas need to work as the customer will try modelling changes. On each sheet there is only one column of formulas that I don't want them to be able to see. I would do what you said however this would take 10-20 minutes each day and would kill the customers ability to model quantity changes. Regards Andrew 'The Ox' Heath Brisbane, QLD Australia "Anne Troy" wrote: You'll be far better off if you first turn the formulas into values, and then save "this worksheet" into a new workbook, and send THAT instead of sending your "calculating" workbook (which you would close without saving). For instance, a macro like this could help you: http://vbaexpress.com/kb/getarticle.php?kb_id=377 ************ Anne Troy www.OfficeArticles.com "Andrew Heath" (delete) wrote in message ... Hi, I have a .xls with 15 worksheets. It consists of 9 sheets containing Bill of Materials and pricing for an individual project. I need to hide a range of formulas on each sheet so that my client cannot view the formula (the formula contains sensitive pricing lookups) I am happy for the customer view the formula result in this range and to play around and see/edit anything else. I need to switch this on and off as I send out each version. Workbook protection is too severe and client cannot do anything. pls help! -- Brisbane, QLD Australia |
#4
|
|||
|
|||
Excel's protection is made to protect intellectual property. It's made to stop
users from overwriting cells. If you put something into a workbook and share it with others, then you won't be able to stop the dedicated from seeing all that stuff. You can lock the cells (format|cells|Protection tab) and hide the formulas (on that same tab), then protect the worksheet (tools|Protection|protect sheet), but this protection can be broken in moments. If you don't want others to see stuff, don't put it in excel--or don't share that excel workbook. Andrew Heath wrote: Hi, I have a .xls with 15 worksheets. It consists of 9 sheets containing Bill of Materials and pricing for an individual project. I need to hide a range of formulas on each sheet so that my client cannot view the formula (the formula contains sensitive pricing lookups) I am happy for the customer view the formula result in this range and to play around and see/edit anything else. I need to switch this on and off as I send out each version. Workbook protection is too severe and client cannot do anything. pls help! -- Brisbane, QLD Australia -- Dave Peterson |
#5
|
|||
|
|||
Excel's protection is NOT made to protect intellectual property.
NOT, NOT, NOT!!! Sorry about that typo! Dave Peterson wrote: Excel's protection is made to protect intellectual property. It's made to stop users from overwriting cells. If you put something into a workbook and share it with others, then you won't be able to stop the dedicated from seeing all that stuff. You can lock the cells (format|cells|Protection tab) and hide the formulas (on that same tab), then protect the worksheet (tools|Protection|protect sheet), but this protection can be broken in moments. If you don't want others to see stuff, don't put it in excel--or don't share that excel workbook. Andrew Heath wrote: Hi, I have a .xls with 15 worksheets. It consists of 9 sheets containing Bill of Materials and pricing for an individual project. I need to hide a range of formulas on each sheet so that my client cannot view the formula (the formula contains sensitive pricing lookups) I am happy for the customer view the formula result in this range and to play around and see/edit anything else. I need to switch this on and off as I send out each version. Workbook protection is too severe and client cannot do anything. pls help! -- Brisbane, QLD Australia -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protecting formulas in a workbook | Excel Worksheet Functions | |||
Can I use formulas that return cell range ref. in charts X series | Charts and Charting in Excel | |||
Copy range of cells omitting formulas that result in " " | Excel Discussion (Misc queries) | |||
Formula Changes when data entered in referenced range | Excel Discussion (Misc queries) | |||
How do I modify range of cells viewed in HTML format? | Excel Discussion (Misc queries) |