Lock formulas but allow movement of cells and deletion of specific columns

[cmoulthrop]

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 taking per account. In the "Current" tab is a combination of formulas, copy and paste of data, as well manual entering. Once completed, the specialist manually moves it to a new sheet "Finished". Is there a way for me to accomplish locking only the formulas that will populate based on the data entered in the sheet? 

[#2]

@cmoulthrop 

Protect formulas or cells without sheet protection from modification

In some situations, the use of blade protection may be undesirable. In order to still protect cells with formulas or any other cells against unwanted changes in these cases, the following procedure can be used.

     Use the Format Cells / Protection command to define which cells are to be protected and which are not. To remove cell protection, simply uncheck Locked.

     Mark the desired cell range or all cells of the worksheet.

     Now open the corresponding dialog box via the Data / Data Tools / Data Validation / Data Validation menu.

     Under Allow, select Custom and enter the following formula: =NOT(CELL("Protection",A1))

     With a click on the OK button, the defined cells are now protected as desired.

Important:

The procedure presented protects the cells from being accidentally overwritten. However, the locked cell can still be deleted with the Delete key. Protected cells can also be overwritten using the copy and paste function. The function presented does not represent particularly good protection, but it is sufficient in practice in many cases.

I would be happy to know if I could help.

NikolinoDE

Was the answer useful? Mark them as helpful!

This will help all forum participants.

[#3]

that doesn't seem to work for me.

[#4]

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!

[#5]

This is extremely helpful. The only problem I had was with the = [equal sign] in the Custom Data Validation field. I found that I had to remove the = [equal sign] from the formula, and then it worked. Thanks so much for your help!