cannot auto filter Excel column 1

[malagash]

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

 

[malagash]

Sorry, using Excel 2003

 

[SkipVought]

Hi,

Check Allow users to use AutoFilter

Skip,

_{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!}

 

[malagash]

Thanks Skip

 

[malagash]

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

http://www.cedarsprings.cc

Thanks

 

[SkipVought]

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,

_{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!}

 

[malagash]

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

 

[SkipVought]

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

Skip,

_{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!}

 

[malagash]

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

http://www.cedarsprings.cc/dieppe2.htm

http://www.cedarsprings.cc/dieppe3.htm

http://www.cedarsprings.cc/dieppe4.htm

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

Thanks for your thoughts

 

[SkipVought]

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,

_{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!}

 

[malagash]

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