Linker IT Software
Order Home
menubar-top-links menubar-top-rechts

SQL*XL: Database to Excel bridge

litLIB: Excel power functions pack

Home Products ExcelLock

Buy now

Download now

Change values in locked spreadsheet.

After locking a spreadsheets to a .lck.xls file both cell values and cell formulas are protected. They cannot be changed or removed and formulas cannot be viewed.

Often however, you may want to allow some of the cells to be edited. ExcelLock facilitates this. Mark the cells in Excel as not locked so they can be edited once you have locked the spreadsheet.

Please consider this short case study to show how this works.

Case study: quotation spreadsheet
In this example we will use a quotation spreadsheet. It is important that the recipient of the quotation can alter the number of items ordered. However the formulas that compute the price cannot be changed and should be locked.

Quotation document

We do not want customers to change the computed fields. ExcelLock will automatically fix all cells with formulas. Just enter the formulas you wish to use and rely on ExcelLock to lock these formulas:
I13: =H13*G13       I14: =H14*G14        I20: =sum(I12:I19)

Field G14 is also a formula cell. The charger is free of charge when more than 10 batteries are purchased. We do not want this formula to be changed by the customer. All formulas will be locked down so this formula is also safe: =IF(H13>10,0,15)

The customer should be able to change the values in the column quantity. To allow these fields to be changed when the quotation spreadsheet is locked you should mark these cells are not locked in Excel. By default all cells are marked to be locked in Excel.

To unlock a cell, right click and select Format Cells. On the protection tab uncheck the locked field. Now the cells can be changed when ExcelLock has locked the file. All cells that have the locked property set cannot be changed.

Unlock cells