Allow sort of table with locked cells. Is this possble?

Glen M

New Member
Joined
Dec 30, 2019
Messages
11
Office Version
  1. 365
Platform
  1. Windows
I searched the previous questions but found no answer.

I have a table and in that table I have some columns that I would like to lock. My users enter data from a user-form. When the form is open the sheet is unprotected, when the user exits the form I want to lock certain columns so the user can't enter data in them manually ( they contain formulas). I can lock the cells no problem and everything is working well EXCEPT - when the user tries to sort the data in the table it wont let them because of the locked cells. I tried the "allow edit range" no dice. I have the correct protections selected in the protect sheet options. Is what I want to do possible?

I am open to any ideas. I thought maybe I could put the formulas in a second table (maybe even on a hidden sheet) and link back to my main table so I could lock the second table but leave the main table open. I don't know just brainstorming ideas so my user can sort but I keep the formulas safe.

Thanks for any help. If its not possible I guess the user will win and have to be careful not to enter data in those cells.
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
Unfortunately you cannot sort data on a protected if any of the cells are locked.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,861
Messages
5,627,315
Members
416,239
Latest member
Counselor85027

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top