HIDING FORMULAS

lzweifel

Board Regular
Joined
Feb 21, 2006
Messages
191
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?
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
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

Board Regular
Joined
Feb 21, 2006
Messages
191
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
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

Board Regular
Joined
Feb 21, 2006
Messages
191

ADVERTISEMENT

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

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
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

Active Member
Joined
Nov 5, 2005
Messages
255
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
 

Forum statistics

Threads
1,136,368
Messages
5,675,358
Members
419,565
Latest member
Phil57

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top