HIDING FORMULAS

[lzweifel]

The left half of my worksheet is meant for the user to enter data - the right columns are all formulas which enter information as the left side is entered.

Is there a way that I can keep my formulas hidden on one side of the sheet and still be able to delete, insert, change shading etc. on the left side of the sheet?

 

[Von Pookie]

Set the cells on the right side to be locked and hidden, and set the left half to be unlocked.

Then protect the sheet.

Edit: The ability to shade, etc. on the unlocked portion can/will depend on the settings you choose when protecting the sheet.

 

[lzweifel]

Thanks Kristy - that is exactly what I did, however when I try to delete a row it won't allow me until I unprotect the sheet.

 

[Von Pookie]

What version of Excel are you using? I can't remember if it exists in 2000 but in 2002 and 2003 there is a list of options available when you protect a sheet. You can check boxes according to what you want the user to be able to do--delete/insert rows and columns is in the list.

However, I just tried to delete an entire row and it would not let me since there were protected cells within the row.

 

[lzweifel]

I am using 2003 - I did check off all the boxes available and still couldn't delete a row.

The fact of the matter is that I don't need to PROTECT the sheet - I just want to hide the formulas - so it is okay to delete the formula or copy and paste it within the sheet - but keep the formula hidden.

It is mainly a copyright thing!

 

[Oaktree]

To avoid taking any chances, how about keeping a formula version for yourself and distributing a "values only" version (where you have copied and pasted values)?

 

[Big Monkey]

What if you defined the formulas as named ranges?

Instead of seeing something like

=counta(A2:A65536, B2:B65536)

They would see

=MyRange

Formula isn't given away but it still works just the same.

Click Insert--->Name--->Define

Enter your formula in the box at the bottom, give it a name at the top, click add and then type "=Whateveryounamedit" in your cell