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
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.
David Brys commented
I just tumbled upon this problem... Nice to know I'm not the only one. Count me in for the petition! :-)
It's difficult to understand why such a basic functional requirement hasn't been addressed yet. My application is similar to many previously mentioned here - it's a glaringly obvious omission that would benefit most if not all users. Resolution required urgently please!
absolutely add the ability to "tab" (add) to the next 'new' table row on a protected sheet. it doesn't make sense (inconsistent) for MS to flag unprotected formula cells in a table and not give the ability to tab to a new row without leaving the sheet unprotected.
Ricardo Diaz commented
Hi Ashvini Sharma, any updates on this front?
[Deleted User] commented
The idea that 'voting up' a suggestion elevates its priority is logically flawed. The priority should be based upon, amongst other things, what competing products can do, how the missing functionality impacts on consultants (like me) and hence on my clients, the risk to data quality and confidence, the level of frustration that users are likely to reach, the ease with which such functionality can be added, the surprise that such functionality is not available and the degree to which uninformed users cannot 'imagine' such functionality. A few random users like us (no disrespect) is not what I'd call a reliable basis for deciding priority. The fact that table rows cannot be inserted in a protected sheet means that Excel tables do not fulfill the promise of 'sets' that I assume they're purporting to represent.