Excel table not expanding with new data

1021557

Board Regular
Joined
Dec 14, 2007
Messages
54
Hi, I created a table in Excel that was working perfectly for other users to enter data. Now it is no longer expanding automatically when new data is entered in the next available row. I have tried everything including checking the auto format as you type in options (it's Excel 2010). I had the sheet protected and when I unprotected it new rows do seem to form part of the table. I have unlocked the cells below and checked allow 'Insert rows' option when it's protected. Any ideas on how to keep the sheet protected while allowing the table functionality to work when new data is entered?

Thanks

Mike
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Hello,

This is a shortcoming of Tables on a protected sheet. The only real way around this is to use a change event with VBA to unprotect the sheet, extend the Table range, then re-protect the sheet. It's very messy. To get rows to insert you would need to have it on an unprotected sheet.
 
Upvote 0
Hello,

This is a shortcoming of Tables on a protected sheet. The only real way around this is to use a change event with VBA to unprotect the sheet, extend the Table range, then re-protect the sheet. It's very messy. To get rows to insert you would need to have it on an unprotected sheet.

OK, thanks very much for letting me know.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top