How to protect some cells in a worksheet 1

[andy1213]

I want to protect some cells of my spreadsheet not the whole worksheet. I want my spreadsheet in a way that, even though it is copied to another sheet, only the values will be pasted not the formula. How can I do that? Thanks!

 

[electricpete]

I think it's two separate questions.

To protect part of a spreadsheet:
1 - Use format / cells / protection and check locked or hidden. Make sure the other cells are not protected or hidden.

The protection is not in place yet. First you need...

2 - Tools/Protection/Protect-sheet or Protect-workbook

To allow someone to copy only the results and not the formula, I think you need to allow them to see only the results and not the formula. Put the formula in some part of the workbook which will later be hidden. In the cell the user will have access to, just add the value of that hidden cell to display it. Then hide that cell so the user can't get to it. I think this will work.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[aerohap]

If you don't want to use the protection options available you can always code a button to copy from another sheet and hide the sheet with the actual formula on it.

 

[andy1213]

Thanks Electricpete! I protected my worksheet. Aerohap, I'll consider your comment too.

 

[cummings54]

For those formulae you want to protect set the protection to hidden. Then protect the worksheet. Now copying the cell will only paste the value of the formula not the formula itself.

To set the hidden property use the format:cells menu item to open the "Format Cells" dialogue. Then select the protection tab. To hide formulae ensure that hidden is checked.

HTH

 

[TDAA]

If you are worried about someone seing the formula, you could also copy and "paste special / values" over what you have. Be sure to have saved your file to another name first.

Excel also allows the use of an image of another sheet within a sheet. This can be used where you want to show cells in a layout that do not match the layout of the sheet.