Issues locking and protecting cells

[rmmhie]

Hi everyone, I am having some problems with locking and protecting my worksheet.

I have an excel table with 7 columns.

Column 1 has data validation; a dropdown list which references a hidden worksheet with a table of values linked to Access.
Column 2 has vlookup formula, which looks up the selected value in column 1.
Column 3, 4, 5 and 6 are all blank to allow user entry.
Column 7 has a simple formula which calculates the product of column 5 and column 6.

By default, all the cells are locked. so i selected column 1, 3, 4 and 5, went to the protection tab and unticked "locked".

Then I protected my worksheet and allowed users to "select locked, select unlocked and insert rows".

The locks work great, but when i insert a row into the sheet, i get the error "the cell is protected blah blah blah".

I tried this in a blank workbook with a simple table and it works fine.

Is it something to do with the vlookup? or the data validation?

 

[rmmhie]

Ok,

Well I have rebuilt my worksheet one step at a time. At every step, I would lock the sheet and try an insert row. I have pinpointed what is causing the error message but I dont know why.

I have a formula which looks like this:

=IF(LEN([@A])=2,[@A],INDIRECT(ADDRESS(ROW()-1,COLUMN())))

The part which is causing the problem is the indirect reference to the cell above. For some reason, when you insert a new row somewhere in the table, because the row numbers are changing its causing some sort of issue.

Any way around this?

 

[rmmhie]

Ok scratch that. Nothing to do with the formula.

Still no solution.