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.
Bookmarks