If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Auto filter sort ascending not working while sheet protected
I have a sheet with a couple of columns that contain formulas, so I have
protected those columns and left the rest of the sheet open for data entry. The problem is, now when I try to sort ascending or descending (on a column containing dates) using the auto filter, it just does not work at all. Any suggestions? -- TommyB |
#2
|
|||
|
|||
Auto filter sort ascending not working while sheet protected
If you have Excel 2000 (and maybe 2002 - I'm not sure exactly when it
was changed) then you will not be able to sort on a sheet which is protected. Later versions did allow certain options when the sheet was protected, but I'm not sure if those extended to sorting. Pete On Jul 10, 11:20*pm, TommyB wrote: I have a sheet with a couple of columns that contain formulas, so I have protected those columns and left the rest of the sheet open for data entry. The problem is, now when I try to sort ascending or descending (on a column containing dates) using the auto filter, it just does not work at all. Any suggestions? -- TommyB |
#3
|
|||
|
|||
Auto filter sort ascending not working while sheet protected
I am using Excel 2003. After locking the cells with the formulas in them I go
to Protect Sheet and it gives me a bunch of check boxes for the things I want to allow users of the worksheet to do. One of them is sort. I check that box and then apply protection. However, when I go back to AutoFilter from the column header and click "sort ascending" it will not allow me to. It says " the worksheet is protected and cannot perform the change you are requesting". Anything else I could be missing here? Thanks for all of your help. -- TommyB "Pete_UK" wrote: If you have Excel 2000 (and maybe 2002 - I'm not sure exactly when it was changed) then you will not be able to sort on a sheet which is protected. Later versions did allow certain options when the sheet was protected, but I'm not sure if those extended to sorting. Pete On Jul 10, 11:20 pm, TommyB wrote: I have a sheet with a couple of columns that contain formulas, so I have protected those columns and left the rest of the sheet open for data entry. The problem is, now when I try to sort ascending or descending (on a column containing dates) using the auto filter, it just does not work at all. Any suggestions? -- TommyB |
#4
|
|||
|
|||
Auto filter sort ascending not working while sheet protected
You can allow autofiltering on protected sheets from 2002 onward.
Autofilter must be in place before protecting the worksheet with "use autofilter" checkmarked. i.e. Arrows must be showing As far as sorting........there is a checkbox to allow sorting, but I have never been able to get Excel to sort on a protected sheet without unlocking all cells, which kinda defeats the purpose of protecting a sheetg Might be a good chance I don't know what I'm doing?? Gord Dibben MS Excel MVP On Thu, 10 Jul 2008 16:27:19 -0700 (PDT), Pete_UK wrote: If you have Excel 2000 (and maybe 2002 - I'm not sure exactly when it was changed) then you will not be able to sort on a sheet which is protected. Later versions did allow certain options when the sheet was protected, but I'm not sure if those extended to sorting. Pete On Jul 10, 11:20*pm, TommyB wrote: I have a sheet with a couple of columns that contain formulas, so I have protected those columns and left the rest of the sheet open for data entry. The problem is, now when I try to sort ascending or descending (on a column containing dates) using the auto filter, it just does not work at all. Any suggestions? -- TommyB |
#5
|
|||
|
|||
Auto filter sort ascending not working while sheet protected
Thanks Gord. Interesting that when I choose to allow sort from their provided
list, it does not work... Oh well... BTW, this group is great. I have learned more in the last month than ever taking classes. -- TommyB "Gord Dibben" wrote: You can allow autofiltering on protected sheets from 2002 onward. Autofilter must be in place before protecting the worksheet with "use autofilter" checkmarked. i.e. Arrows must be showing As far as sorting........there is a checkbox to allow sorting, but I have never been able to get Excel to sort on a protected sheet without unlocking all cells, which kinda defeats the purpose of protecting a sheetg Might be a good chance I don't know what I'm doing?? Gord Dibben MS Excel MVP On Thu, 10 Jul 2008 16:27:19 -0700 (PDT), Pete_UK wrote: If you have Excel 2000 (and maybe 2002 - I'm not sure exactly when it was changed) then you will not be able to sort on a sheet which is protected. Later versions did allow certain options when the sheet was protected, but I'm not sure if those extended to sorting. Pete On Jul 10, 11:20 pm, TommyB wrote: I have a sheet with a couple of columns that contain formulas, so I have protected those columns and left the rest of the sheet open for data entry. The problem is, now when I try to sort ascending or descending (on a column containing dates) using the auto filter, it just does not work at all. Any suggestions? -- TommyB |
#6
|
|||
|
|||
Auto filter sort ascending not working while sheet protected
Yea it remains strange that you have a checkbox "allow users to use
filter/auto filter" excel than blocks this option. So Microsoft: PLEASE FIX THIS BUG!!. On this website numerous posts on this issue, many people experience this problem. Using macro is complex and unnecessary when the checkbox would work as required. Macro's are also not ideal because im making sheets who needs to be bullet proof, for less experienced users. PS: Im using excel 2007 on XP with all updates. Thanks other users of this forum for your post, all helpful. brgds Olivier "TommyB" wrote: Thanks Gord. Interesting that when I choose to allow sort from their provided list, it does not work... Oh well... BTW, this group is great. I have learned more in the last month than ever taking classes. -- TommyB "Gord Dibben" wrote: You can allow autofiltering on protected sheets from 2002 onward. Autofilter must be in place before protecting the worksheet with "use autofilter" checkmarked. i.e. Arrows must be showing As far as sorting........there is a checkbox to allow sorting, but I have never been able to get Excel to sort on a protected sheet without unlocking all cells, which kinda defeats the purpose of protecting a sheetg Might be a good chance I don't know what I'm doing?? Gord Dibben MS Excel MVP On Thu, 10 Jul 2008 16:27:19 -0700 (PDT), Pete_UK wrote: If you have Excel 2000 (and maybe 2002 - I'm not sure exactly when it was changed) then you will not be able to sort on a sheet which is protected. Later versions did allow certain options when the sheet was protected, but I'm not sure if those extended to sorting. Pete On Jul 10, 11:20 pm, TommyB wrote: I have a sheet with a couple of columns that contain formulas, so I have protected those columns and left the rest of the sheet open for data entry. The problem is, now when I try to sort ascending or descending (on a column containing dates) using the auto filter, it just does not work at all. Any suggestions? -- TommyB |
#7
|
|||
|
|||
Auto filter sort ascending not working while sheet protected
I had issues with this too. In situations where I want to just share data,
I do the following. 1. I do not use the worksheet protection. 2. I password protect the workbook. 3. To share the data with others, I create a .CSV version(sorted or whatever). "Olivier" wrote in message ... Yea it remains strange that you have a checkbox "allow users to use filter/auto filter" excel than blocks this option. So Microsoft: PLEASE FIX THIS BUG!!. On this website numerous posts on this issue, many people experience this problem. Using macro is complex and unnecessary when the checkbox would work as required. Macro's are also not ideal because im making sheets who needs to be bullet proof, for less experienced users. PS: Im using excel 2007 on XP with all updates. Thanks other users of this forum for your post, all helpful. brgds Olivier "TommyB" wrote: Thanks Gord. Interesting that when I choose to allow sort from their provided list, it does not work... Oh well... BTW, this group is great. I have learned more in the last month than ever taking classes. -- TommyB "Gord Dibben" wrote: You can allow autofiltering on protected sheets from 2002 onward. Autofilter must be in place before protecting the worksheet with "use autofilter" checkmarked. i.e. Arrows must be showing As far as sorting........there is a checkbox to allow sorting, but I have never been able to get Excel to sort on a protected sheet without unlocking all cells, which kinda defeats the purpose of protecting a sheetg Might be a good chance I don't know what I'm doing?? Gord Dibben MS Excel MVP On Thu, 10 Jul 2008 16:27:19 -0700 (PDT), Pete_UK wrote: If you have Excel 2000 (and maybe 2002 - I'm not sure exactly when it was changed) then you will not be able to sort on a sheet which is protected. Later versions did allow certain options when the sheet was protected, but I'm not sure if those extended to sorting. Pete On Jul 10, 11:20 pm, TommyB wrote: I have a sheet with a couple of columns that contain formulas, so I have protected those columns and left the rest of the sheet open for data entry. The problem is, now when I try to sort ascending or descending (on a column containing dates) using the auto filter, it just does not work at all. Any suggestions? -- TommyB |
Thread Tools | |
Display Modes | |
|
|