Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

cannot auto filter Excel column 1

Status
Not open for further replies.

malagash

Technical User
Aug 29, 2006
252
CA
I find that I cannot auto filter a column in Excel if any of the cells in adjacent columns are locked and the worksheet protected.

Does anyone know a work around for this?

Thanks
 




Hi,

Check Allow users to use AutoFilter

Skip,
[sub]
[glasses] When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![tongue][/sub]
 
I am revisiting this post.

The "Allow users to use AutoFilter" works fine however when I try to Sort all columns (i.e. ascending or descending) I get the error "the cell you are trying to change is protected and therefore read-only".

When protecting the worksheet I did check the Sort option along with Use Auto Filter and Auto Filter works but when I try to Sort all of the data entered I get the error message.

I discovered that there were two hidden columns however the issue remained after the hidden columns were removed.

I have to be able to sort the data in order to, as a minimum organize it by date.

I have placed a copy of the spreadsheet at
Thanks
 




I did some testing, something that you could do as well. Part of becomeing more expert in an application is trying all sorts of things to see HOW they work. So I did that and found out something that I did no know before.

I discovered that in order to SORT, all the cells of data and the column heading in the column you are sorting must be unlocked Strange, but unlocking the column heading makes SORT available in a protected sheet.

If there is another way, I'd like to know.

Skip,
[sub]
[glasses] When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![tongue][/sub]
 
Thanks Skip:

In inspecting the columns that are protected there is no header. When I try Data | Sort - there is no header however, I might not be looking at the right thing though as per your post.

I have found a work around which is satisfactory but I would like to sort this out somehow.

As a workaround I placed the protected columns to the far right of the sheet and when sorting all columns to the left of them, they sort appropriately correctly.

I would upload a zip of the file for you to look at but I don't think it can be done at Tek-Tips
 




I'd dare say that the ONLY columns sorted are the unlocked ones, not ALL ROWS.

Skip,
[sub]
[glasses] When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![tongue][/sub]
 
Actually, you are correct. The unlocked columns in the contiguous range of columns do sort correctly and the two locked columns only appear to sort.

The reason is (in my mind) quite simple. The contents of the two protected columns are formulas. Once I placed the two locked columns to the right of the range that is being sorted the formulas in those rows still work so in fact, those two columns do not get sorted purse, they just reflect the results from the formulas.
You can see what I mean by following these three links

I understand that protected columns can be sorted using a macro however that is beyond me at this point.

Thanks for your thoughts
 



Of course, this can easily be done with macros.

But if you want the users to use native Excel features, you're restricted when you protect.


Skip,
[sub]
[glasses] When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![tongue][/sub]
 
Unfortunately I don't have much leeway with this computer. It is controlled by the IT guys and they won't let me lower security settings.
I shall experiment on my home unit
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top