Protecting Formulas from a range from being viewed

[Andrew Heath]

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!

 

[Anne Troy]

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]

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

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

 

[Dave Peterson]

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.

 

[Dave Peterson]

Excel's protection is NOT made to protect intellectual property.

NOT, NOT, NOT!!!

Sorry about that typo!