Protecting a Sheet


S

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?
 
Ad

Advertisements

D

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.
 
S

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.
 
P

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
 
S

Shannan

What is a pivot table?

Peggy Shepard said:
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
 
G

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
 
Ad

Advertisements

Ad

Advertisements

P

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


Shannan said:
What is a pivot table?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top