Forum Discussion
Lock formulas but allow movement of cells and deletion of specific columns
Hello, I have tried so many different ways to try and lock my formulas but for some reason I can't get it to do what I want. This tracker is to collect data on how many actions each specialist is tak...
Good day!
Can I just clarify, for the deletion of columns, is it a necessary step or can it be avoided? Also, your Current tab and Finished sheet, are they in the same excel file or two different files?
I believe for your query, your formulas would be extracting data from what is manually entered or pasted into the sheet by your specialist, yes? If so, for the formulated cells, you could select those cells, right-click and then select “Format…”. A pop up will appear - go to the “Protection” tab and check the “Locked” option.
For the cells that your specialist will be manually keying/pasting data in, select those, right-click, select “Format cells”, go to the “Protection” tab in the pop-up and uncheck the “Locked” option.
After doing the above, go to the “Review” tab on your Ribbon and select on “Protect Sheet”.
A pop up box will appear again. Here, password is optional but I strongly recommend keying it in so that one no one can change the formulas on the sheet. Also, you have several options you can choose that allows the users to manipulate the sheet the way they need to, by checking “Select locked cells”, “Select unlocked cells”, “Delete columns”, “Use AutoFilter” and so on. After checking the options that suits your needs and keying in the password for the sheet (please remember it! Or no one else will be able to unlock the sheet), press okay and the sheet is now locked.
Your specialist should then should be able to manually enter in or paste data in the cells that you unlocked earlier, delete columns where the formulated cells are not at and all. They should also be able to copy and paste the information onto a new sheet.
Or, instead of manually moving it to the new sheet, you can also use formulas to extract information from the Current tab into the Finished sheet. Then these would omit the need to paste the information from Current into Finished.
Hope this helps and do let me know if it works for you! Would be really happy if it did help!
Can I just clarify, for the deletion of columns, is it a necessary step or can it be avoided? Also, your Current tab and Finished sheet, are they in the same excel file or two different files?
I believe for your query, your formulas would be extracting data from what is manually entered or pasted into the sheet by your specialist, yes? If so, for the formulated cells, you could select those cells, right-click and then select “Format…”. A pop up will appear - go to the “Protection” tab and check the “Locked” option.
For the cells that your specialist will be manually keying/pasting data in, select those, right-click, select “Format cells”, go to the “Protection” tab in the pop-up and uncheck the “Locked” option.
After doing the above, go to the “Review” tab on your Ribbon and select on “Protect Sheet”.
A pop up box will appear again. Here, password is optional but I strongly recommend keying it in so that one no one can change the formulas on the sheet. Also, you have several options you can choose that allows the users to manipulate the sheet the way they need to, by checking “Select locked cells”, “Select unlocked cells”, “Delete columns”, “Use AutoFilter” and so on. After checking the options that suits your needs and keying in the password for the sheet (please remember it! Or no one else will be able to unlock the sheet), press okay and the sheet is now locked.
Your specialist should then should be able to manually enter in or paste data in the cells that you unlocked earlier, delete columns where the formulated cells are not at and all. They should also be able to copy and paste the information onto a new sheet.
Or, instead of manually moving it to the new sheet, you can also use formulas to extract information from the Current tab into the Finished sheet. Then these would omit the need to paste the information from Current into Finished.
Hope this helps and do let me know if it works for you! Would be really happy if it did help!