Longtime fan, first time poster; apologies if I somehow messed up thread etiquette. Using Excel 2010.
For various reasons which aren't worth going into (basically I need to protect/lock an entire table with the exception of one column, but still allow sorting) I need to make use of Excel's "allow users to edit ranges" feature on the entirety of the range Table1. When I enter =Table1 as my cell reference in the dialogue box and click OK, however, it shows the range as all rows currently within the table. When I add new rows to the table, the range does not expand to the entirety of Table1; instead, it is limited to the cells which were originally part of Table1, when I specified the range which users are allowed to edit.
Is there a way to make the "allow users to edit ranges" feature dynamic? That is, can it expand with a table?
This is important since I'm configuring a template, which an in-house application pastes data into depending on specific customer accounts. I cannot predict how many rows I need users to be able to edit.
Thanks in advance for the help. I'm 99% certain I need this solution since there seems to be a bug in Excel 2010's "Protection" feature which prevents users from sorting the cells of a locked table even if "Sort" and "Autofilter" are checked. I know there are VBA workarounds here, but I'd like to keep the workbook macro-free for now. My current solution is to lock everything but my header row and a "confirmation" column which is used to flag each row, specify that users cannot select locked cells, and then allow users to edit the entirety of Table1. This means that they'll be able to sort the table and select values in the "Confirmation" column, but will be unable to select the locked cells.
Sorry for the run-on explanation. I've had too much coffee and am banging my head against the wall on this issue.
For various reasons which aren't worth going into (basically I need to protect/lock an entire table with the exception of one column, but still allow sorting) I need to make use of Excel's "allow users to edit ranges" feature on the entirety of the range Table1. When I enter =Table1 as my cell reference in the dialogue box and click OK, however, it shows the range as all rows currently within the table. When I add new rows to the table, the range does not expand to the entirety of Table1; instead, it is limited to the cells which were originally part of Table1, when I specified the range which users are allowed to edit.
Is there a way to make the "allow users to edit ranges" feature dynamic? That is, can it expand with a table?
This is important since I'm configuring a template, which an in-house application pastes data into depending on specific customer accounts. I cannot predict how many rows I need users to be able to edit.
Thanks in advance for the help. I'm 99% certain I need this solution since there seems to be a bug in Excel 2010's "Protection" feature which prevents users from sorting the cells of a locked table even if "Sort" and "Autofilter" are checked. I know there are VBA workarounds here, but I'd like to keep the workbook macro-free for now. My current solution is to lock everything but my header row and a "confirmation" column which is used to flag each row, specify that users cannot select locked cells, and then allow users to edit the entirety of Table1. This means that they'll be able to sort the table and select values in the "Confirmation" column, but will be unable to select the locked cells.
Sorry for the run-on explanation. I've had too much coffee and am banging my head against the wall on this issue.
Last edited:
