Protecting a Sheet

[Shannan]

Hi,
I've turned on the "protect sheet" feature in order to be able to lock
certain cells and hide the formulas in these locked cells. When i turned this
feature on, i clicked that i wanted to allow all users of this worksheet to
sort. However, when i highlight the rows i want and try to sort them, it
won't let me. A window pops up saying "the cell or chart you are trying to
change is protected and therefore read-only.". How do i lock certain cells so
that people cannot type over the formula, but still be able to sort the
spreadsheet?

 

[Dave Peterson]

I think you'll find that you can only sort the range if all the cells in that
range are unlocked.

And you can still hide the formulas in those unlocked cells by using the
protection tab of the Format|Cells dialog. The formulas will be hidden when you
protect the sheet.

So my questions to you are:
Are all the cells in the range to sort unlocked?
Did you select just the range to sort--or did you really select the entirerow
(which may contain locked cells)?

I've found that my life is a little easier if I can leave empty rows and empty
columns that surround the range to sort. It's not necessary, though.

 

[Shannan]

I selected the entire row to sort and the row contains columns where all the
cells have been locked. All the information across a row pertains to one
person, so when i sort the spreadsheet by a certain column, i need to sort by
the rows, not by cells in that column. But i need to lock the cells in
certain columns so that someone doesn't accidentally type over a formula.

 

[Peggy Shepard]

Hi Shannan,

I believe this is a bug in Excel. You can leave cells locked and enable
filtering - however it doesn't allow you to sort locked cells even if you
select to allow sorting.

You might consider using a pivot table as a work around. Select Use Pivot
Table Reports when protecting the worksheet.

Peggy

 

[Shannan]

What is a pivot table?

Hi Shannan,

I believe this is a bug in Excel. You can leave cells locked and enable
filtering - however it doesn't allow you to sort locked cells even if you
select to allow sorting.

You might consider using a pivot table as a work around. Select Use Pivot
Table Reports when protecting the worksheet.

Peggy

 

[Gord Dibben]

Not a bug.

Think about it................filtering does not change the location or
content of cells.

Sorting changes location, so locked cells in the sort range will not sort.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Have you tried looking for Pivot Table in Excel help?

For excellent instructions on Pivot Tables see Debra Dalgleish's site.

http://www.contextures.on.ca/tiptech.html

Scroll down to "P" section.


Gord Dibben MS Excel MVP

What is a pivot table?

 

[pshepard]

Hi Shannon,

In Excel 2007 -

Insert|PivotTable|Pivot Table

Then follow the wizard's set of question/instructions. Pivot tables are easy
to learn - but this should help with a quick start.
--
If this post helps click Yes
---------------
Peggy Shepard

What is a pivot table?