Need to use array function to protect a range, rather than protecting worksheet.

Hello,

I'm attempting to protect a range of cells by using an array function rather than protecting the worksheet. The worksheet has a pivot table on it and I wish to keep it, but the pivot table cannot be updated on a protected worksheet. In the table below, Column A represents job assignment, and Column B represents scheduled hours. In Column A, I select from a drop-down list. Cell B1 contains the following formula: IF(A1="absent", 0, Sheet2!$D$1). I then dragged the formula in B1 down through B4.

A B
1 absent 0
2 pick 8
3 load 8
4 sort 8

This formula works fine, but again I am looking to protect column B by using an array function. Is it possible to write a single formula for the cells in column B that looks at the cell directly to the left of the selected cell as opposed to looking at a cell address so that I may CSE and make it an array function? Or am I going about this the wrong way. Again the real problem is that I need to protect the formulas in Column B but cannot protect the work sheet because of an active pivot table on the same sheet.

Kind regards.

Hi David
You can protect certain cells on a worksheet without locking the whole worksheet.
Follow these steps:
1. Right click on the grey cell between the first column and row (top left hand corner of your worksheet) and select Format Cells.
2. Click on the Protection Tab.
3. Un-tick the Locked tick box.
4. Click OK.
5. Select the whole of Column B by clicking on the Columns Header.
6. Right click on the column B header and select Format Cells.
7. Put a click in the Locked box and click OK.
8. Click on the Review Tab.
9. Click on Protect Sheet.
10. Add a password if you wish and any other selections you want to make and then click OK.
Column B will then be locked to any changes but all other cells on the worksheet will be open to change/amendment.
Hope this helps.
Good luck.
Tony