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
Found a partial workaround to 'append' data but determining the size of the table and subsequently using range/cells/offset to get an entry right below. The table and any column formula will create the new record with the auto-expand function, from which point on Listobjects() can be used to further add data to that and any other records. The UserInterfaceOnly:=True in the Thisworkbook module's Workbook_Open macro sets the prerequisite for macros to be allowed the manipulation on protected sheets with locked cells.
Just ran into this brick wall. Microsoft pitches using tables and structured references and then gives no way to lock the formulas down in an expandable table except data validation (slows things down so much it takes a few seconds to add a row) or VBA. If Microsoft wants large organizations to work their way up to power BI, it'd be a lot easier if I could demonstrate with tools mgt already has confidence in (e.g. tables, spreadsheets, etc.)
Robert Robson commented
This is 7th most voted open idea on the list and the comment from the admin didn't change for almost 2 years.
The discussion on using VBA and Data Validation shows just how important it is for Microsoft to do this properly rather than forcing people into messy workarounds.
@Pavlo Burchenko: Yes, the error checking rules apply to the local copy of Excel where they are set. However, you can disable error checking on workbook open and then restore the user's previous settings on workbook close with a little VBA: https://www.ozgrid.com/forum/index.php?thread/29089-disable-error-checking-via-vba/&postID=1035971#post1035971 .
Still looking for a way to disable just "Data entered in a table is invalid" with VBA. It doesn't appear to be one of the properties offered in the ErrorCheckingOptions object: https://docs.microsoft.com/en-us/office/vba/api/excel.errorcheckingoptions . The closest I could find, "Application.ErrorCheckingOptions.InconsistentTableFormula = False" disables "Incosistent calculated column formula in tables" instead.
Pavlo Burchenko commented
@Miles Wolbe: Thanks for the links!!! Regarding Data Validation workaround, hinding error notifications will only work for your workstation. Other users will see the error notifications it once opened on their workstations. This is because such settings are local and independent from the file.
@DMT: Thanks for the heads up. You're right; while the data validation hack works to protect cells from users typing into them, it does not prevent users from pasting into them. However, an error is displayed with an option to "Restore to Calculated Column Formula", even if the "Data entered in a table is invalid" error checking rule was disabled as previously described.
@Miles Wolbe - but the data validation is not working, since users can just copy-paste on top of validation and over-write all your rules.
In the meantime, some VBA workarounds:
Autoexpand Excel Tables on Protected Sheets https://www.excel-first.com/autoexpand-excel-tables-on-protected-sheets/
Protect sheet but keep table expandable https://www.excelforum.com/excel-general/1115066-protect-sheet-but-keep-table-expandable.html#post5140971
and a neat Data Validation trick to protect cells without having to use Protect Sheet:
Protect Cells in Excel Using Data Validation https://www.accountingweb.com/technology/excel/protect-cells-in-excel-using-data-validation
Since your formulas protected via Data Validation will likely show green flag errors, hide them via File > Options > Formulas > Error checking rules > uncheck "Data entered in a table is invalid".
Any information on whether there is any intention to address this issue?
Any solution as yet ?
Any update after 3 years?
Aside from visual basic, are there any good methods for us to insert or delete rows in a protected sheet without giving up passwords to the users?
Oliver Wilcock commented
Thanks, Eckhard, for expanding the scope of what could be much better about structured tables. I've also been frustrated by data validation along with the show-stopper protected sheet issue. I didn't know the workaround you described.
I've also cursed Microsoft when trying to retrieve data through ODBC/Jet. The table is not stored as a range and Jet doesn't find it.
I've found business users very reluctant to embrace tables and I think the largest barrier is the behaviours when adding, removing and especially moving columns (cut and paste). I haven't fully wrapped my head around what it does. I know it messes up the column widths. The workaround I use is to insert a new column in the sheet and then to cut and paste the table "Entire Table Column" into the new location and then delete the unwanted sheet column. Unnecessary hassle.
"by desgin" is a euphemistic paraphrase of the fact that you [Microsoft] yourself do not believe in the high value and importance of the Structured Tables (→ ST) - or did not recognize them.
The productivity of Excel increases massively if you [Microsoft] would consistently support the possibilities of ST.
Here are a few examples of shortcomings (-) and errors (--):
- Data Validation does not refer to the columns of a table, but only to the "normal" coordinates.
(annoying workaround: Range name Lastname=StructuredTable[LastName])
Fix column (e.g. $C3* $D3) and copy to the right (Ctrl + R) must be very laborious to formulate in ST:
=StructuredTable[@[Price]:[Price]] * StructuredTable[@[Quantity]:[Quantity]]
$C3 → StructuredTable[@[Price]:[Price]]
$D3 → StructuredTable[@[Quantity]:[Quantity]]
-- Conditional format leads to crashes when applied within a ST
-- Formats (especially styles) are not reliably pulled along when the ST is extended downwards.
Miles Williams commented
This is absolutely CRITICAL!!!
But, I really think this is a MUST HAVE!
Vladimir Vladimirovich Putin commented
Эй, я согласен с этим.
У меня есть лист, где у меня есть таблица с несколькими столбцами. Мне нужно убедиться, что мой пользователь никогда не изменяет определенный столбец, который содержит формулу. Я хотел бы, чтобы мой пользователь все еще мог добавлять строки в мою таблицу с помощью клавиши TAB и изменять все другие ячейки таблицы, которых нет в упомянутом столбце.
Donald Trump commented
How i wish excel solve this!
In some of our templates, we protect (or lock) the sheets using a password. This is done to prevent the user from accidentally editing/removing the formulas used. This is not done to keep the formulas secret. We give the password to the customer so that they can unprotect and edit if they are familiar with Excel and formulas.
Pavan Kumar Nookala commented
Hey, really ist a big bug in Excel. How can this be ignored.
I am an employee for Projekt-Management, I work with these Excel Sheets which involves data entry from different Clients. Either I should not involve any formulas in my unprotected Excel sheet or share the Password every time form data entry.
Please look into this.
The Problem is same for many People but most of them dont know that they can Vote and prioritize their Problem.