2003 | Protected Sheet | Data List - How?

**TMShucks** · 12-10-2010, 08:21 AM

I hope the title is succinct enough but, at the same time, explains my problem.

I am working at a company who use Excel extensively. One of the workbooks has several worksheets, each with 10,000 rows pre-populated with 5 to 8 formulae. The sheets are protected.

The idea is to save the users from having to input any formulae themselves and to minimise user error by deleting or changing the formula.

As a consequence, the workbook is about 22Mb and very slow, and impacts on other workbooks the user may have open. Recalculation is horrendously slow.

It seemed to me that the Excel 2003 Data List option was exactly what was required. I removed all the dross, did some tidying up and Created Lists on the relevant sheets.

Everything, in the garden appeared rosy, as they say ... until I re-protected the sheets. Where, previously, selecting a cell in the List caused a "new line" to appear at the end of the List, ready for data to be entered and formulae to be populated (automatically), this no longer happens.

Does anyone have any thoughts on how I can overcome this issue?

Edit: the question is not, how do I create a Data List but how do I make a Data List operate as it should on a Protected Sheet.

Thanks in advance.

**NBVC** · 12-10-2010, 08:28 AM

Not sure if it worked for the OP on this thread.. but perhaps a suggestion to try, nonetheless....

http://www.ozgrid.com/forum/showthre...t=55727&page=1

**TMShucks** · 12-10-2010, 09:53 AM

Thanks for the suggestion but I was hoping to avoid using code.

I just wanted to use the Excel 2003 Data List feature but on a Protected worksheet.

The solution provided appears to assume that VBA is being used to insert rows. That's not what I'm looking for. The Excel 2003 Data List will "add" a new line and, as soon as some data is entered, will populate the formulae. C'es parfait! But not on a protected sheet, it seems.

Regards

**NBVC** · 12-10-2010, 10:14 AM

I am not sure you can do it without VBA.. none of the protect sheet allow options indicate the allowance of adding to the Lists.. not even in XL2007...