Get Tables working on protected sheets (add rows, sort, filter, etc.)
I would like to be able to, under the right circumstances, have the ability to add/remove rows of a table which resides on a protected sheet.
Currently if you protect a sheet you can't insert/delete rows of a table. Even if you unprotect all cells and allow inserting/deleting of rows on sheet protection, it doesn't matter and is completely ignored.
What I am proposing would have to follow specific rules:
- Table cells would have to be unprotected
- Cells below table would have to be unprotected (truly optional † )
- Insert Rows would have to be specified in sheet protection
† The way table rows are inserted is different than a standard row insert. If there are blank rows below the table it will "consume" those rows, and not shift things down. If there is data below the table, once it consumes all rows between the two, it will start inserting. This mechanism would make the second item above optional depending on how you [Microsoft] would handle this internally. My preference would be to have the cells unlocked, and honor the protection status of the cell, and not overwrite, or "consume" if there is space available.
This has been BROKEN for some time. Since we can ALREADY do this with standard cells, it should ALSO work for tables. This request has come up multiple times, and has previously been rejected as "BY DESIGN", but I'm here to tell you - and I'm an expert - this is not by design, and is a bug which has been overlooked and should be fixed.
(2016-12-07 Dan B [MS]: updated title to capture the actual ask here, which is to make Tables work better on protected sheets; currently, since protected sheets were built "before" Tables, they don't work well with "Tables" or objects that adjust/expand within the grid as users interact with them)
Thanks for logging this great suggestion, Zack, and to others for voting it up. We’ll prioritize this according to the number of votes, so if there’s more interest, please make sure to register your vote!
Lead Program Manager
Michael Bohannon commented
I too have the same issues and described in great detail in many of the previous comments. Please fix this bug and make the tables have the ability to expand and keep formatting when expanded - while protected.
Dear MS Excel,
PLEASE make this suggestion a reality. I work with tables that contain numerous formulas and functions that I do not want to inadvertently corrupt because I fail to pay attention while entering data in other cells. Locking down the cells containing formulas in my tables while maintain the functionality of automatically resizing the table by adding new rows for additional data would be a HUGE time saver and ensure data integrity
I 100% agree this is a major gap that needs to be fixed asap ! thanks
Data validation does not prevent wiping out everything in a cell simply by pressing the delete key or copying and pasting from another cell, even if Ignore Blanks is unchecked.
No warnings, it just happens!!!
Yet another "BUG" mess that needs to be fixed.
Only a protected worksheet or VBA code can prevent this and most companies don't want macro enabled worksheets. Not out of fear brought on by the malicious code warnings or because they think the code you've written may be doing bad things, but just the administrative nightmare for the IT department to manage which ones there going to allow to be run.
If you're lucky and the error checking rules haven't been turned off out of frustration you may get a little green triangle that's designed to confuse the average user, i.e. department head, when they hover over it and up pops a vague message designed for programmers. They're not going to think that it's something they've done wrong, nor should they. They're going to call you to come and fix it and when you explain you can't, it's part of how excel works, they won't believe you and you end up turning of the little green triangles and now you're caught in an infinite loop with the understanding of what it must feel like to be a computer with a dodgy programmer.
Andrew Ashurst commented
Data validation is a usable stop gap if you have a limited distribution but I don't think its a substitute for what's being asked for here - which is, the ability to add a row to a table on a protected worksheet.
This would be a very useful feature. At present if I need to add an additional row just to add some space let alone additional data analysis into the protected formula section, I unprotecting and reprotecting for each worksheet. This is fine, if we are close to the beginning of a project. Once I have >50 it's painful. In fact, is there any chance of allowing a power editing password? Something that would allow me to release the protection for the workbook to tweak a given formula without doing so for each sheet individually.
Accepting data validation is a flawed solution you can use the method and simplify application.
In the first data row of the table select all the cells that contain formulas.
Set data validation for all of them in one go.
On settings tab allow Custom, untick the ignore blanks box.
In the formula box type, "This cell contains a formulas - do not change it".
Select this text and copy it.
On the Input Message tab paste this same text as the Input message.
On the Error Alert tab paste this same text as the Error message.
Unless your user tries to type in, "This cell contains a formulas - do not change it", they will fail and get a helpful message.
They can still copy and paste something in to the cell and bypass the check.
I am thinking even if MS do sort out table protection this is still quite a useful thing to do.
Also isn't copy and paste not only over ruling data validation but deleting data validation a problem that needs to be fixed anyhow?
Luc Mettepenningen commented
Data Validation (List) DOES expand with tabels. (Excel 2016 version 16.19 for MAC)
The idea of using Data Validation to prevent cell overwriting is flawed. If users paste data into the cell the Data Validation is overwritten.
On a positive note. I have found that if applied to All the cells in a specific Table column the Data Validation, Cell formatting and conditional formatting IS copied down when a new table row is generated. However, as soon as one cell is 'pasted into' then this "cell properties copy down" feature no longer works.
I started using data validation, no macros or VBA. First construct your table like usual along with the formulas you need. Then select the top formulated cell of that row and go to data validation, select the correct "value" (in my case "whole number", even tho it's currency it still works) then enter the same formula you created in that cell in both the minimum and max bar. Be sure to include absolute references where needed. Important; uncheck "ignore blank" before you hit okay. Then press okay.
Copy this cell down to the end of the worksheet since data validation does not expand with the tables.
Hope this helps. In my case it works seamlessly and I don't need to protect that sheet since you can't enter any value in formulated cells other than the calculated value.
Please include the ability to insert a row into a protected table. This function is long over due.
If you go to control.cs in "referencesource.microsoft.com" which is part of the source code for C# and has been made available for all to read by Microsoft, read line 9801 which is part of the comments for the PaintWithErrorHandling function.
This may give you some insight as to what at least one Microsoft coder thinks' of their user's (our) coding ability and why our requests here may simply be ignored.
But just in case, I vote yes to this feature (bug fix) also.
Apologies in advance if I've broken any TOS here.
Bryan Sonnier commented
Came across this thread and Yes!! so much Yes! We need to be able to lock the formula column of a table and still keep it expandable without having to go through a bunch of VBA trouble.
Bert Neyens commented
As one of many others I also believe this would be great: Please implement tomorrow... or better today! Or no: YESTERDAY!
The table-options are great, but when you cannot protect your formulas while having other people using/adding data to the table, you end up constantly fixing (unintended) formula-errors because not everyone is skilled in excel....
Simon Hurst commented
Would it help speed things up if you were to think of this as correcting a serious bug rather than as an enhancement?
MICROSOFT! WHY HAVENT YOU DEALT WITH THIS? PREVENTING THIS FUNCTIONALITY IS BEYOND A JOKE, GET IT WORKING NOW!!
You need to get this straightened out. I just ran into this problem. I created a rather simple Project Tracking tool designed to take advantage of the automatic addition of rows allowed by Tables. Since this tool is to be distributed fairly broadly and each project workbook will be maintained in a Sharepoint environment where the Excel inept can access it, I need to protect the formulas and formats and maintain the designed functionality.
Why hasnt this been done yet Microsoft? COME ON!!!!
I do not want to use VBA to achieve this functionality. Better if Microsoft can provide this without the help of VBA.