Would you know how to get a report of all the locked and unlocked cells of an Excel sheet? Doing it manually will be so painful…
I know this is not directly validation-related, but I would find it very helpful to know this… Thanks!
I know your pain I once had to do a similar test, as far as I know I dont have any quick fix for this, perhaps there maybe some script out there that you could run to acknowlege locked or unlocked cells.
Of couse if there is that would have to validated to ensure it works correctly!!!
tough luck… thanks anyway!
You can use the Excel formula:
CELL(“protect”,A1)
If cell A1 is protected, the formula returns a ‘1’, unprotected returns a ‘0’.
Create another copy of the sheet you wish to examine, enter the above formula in cell A1 (replace the A1 reference with a reference to the sheet your examining -‘Sheet1!A1’). Copy the formula to all the other cells, so the reference changes for each cell.
You should get sheet showing '0’s and '1’s. The '0’s indicate the corresponding cell is unprotected, the '1’s indicate the corresponding cell is protected.
Not sure if this would be a ‘validated’ method of checking, but it is quite handy and practical.
Nice information, must remember that next time