I have a spreadsheet that has multiple tabs and many Tables that reference each other. The Tables have both data and formulas. When locking a column in a Table with a formula, the entire Table becomes locked. I would like it such that I can lock the columns with formulas but allow users to sort the table but not edit it. Any suggestions? Thank you.
When you protect the sheet, ensure that you tick to allow auto filters in the options list.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
I tried that but still get an error that says "the cell or chart you are trying to change is on a protected sheet"
When I protect the sheet, I have every box checked except "Select Locked cells"
Complete overkill. For normal purposes the top two plus the one that allows auto filtering will suffice. Try that.
https://support.office.com/en-us/art...3-f4ca36276de6
Last edited by AliGW; 07-15-2019 at 04:42 PM.
I tried that, then you could not select on ANY cells. I then allowed user to "edit unlocked cells" and "Sort". With this change, you can change any cell in the sheet EXCEPT for the cells that I locked in the column of the table, and then also the table headers became locked, thus eliminated the Sort function. Ughhhhh.
The information on Microsoft's site says "Users can't sort ranges that contain locked cells on a protected worksheet, regardless of this setting." in reference to checking the "Sort" box upon protecting a sheet.
Any known workarounds?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks