Forum Discussion
Conditional Formatting - how to minimize formatting rule duplication with editing
Hi all, I built a spreadsheet for management to track project deliverables, and am running into two issues. I attached a slimmed down version of the spreadsheet for review. 1. The file co...
Hi,
I am attaching a short tutorial (2 min) on protecting and Hiding your formulas, so no one could see or modify them.
https://www.youtube.com/watch?v=GywkmsQSiYA
Hope that helps
Nabil Mourad
nabilmourad Thank you Nabil, the YouTube video was very helpful! I'm still running into one small issue, however. When I go to Protect the Sheet, the formulas are protected and hidden (good), but the end user cannot insert rows. I need them to be able to insert rows and have the new rows carry over my protected/hidden formulas. Is this possible? I see an "Allow Edit Ranges" button but am not sure if this would be helpful?
Thanks,
Casey
Hi Casey,
Glad the video helped.
The situation is:
In any Excel spreadsheet we have 17,179,869,184 cellsAll the 17 Billion cells are locked by default
Protection on the Review Tab says: "Protect Contents of LOCKED cells"
Which means, if you want to allow the user to edit a certain range of cells, Select that range >>hit CTRL +1>> Protection Tab >> Uncheck "Locked" >> then Review Tab >> Apply Protection.
In the Protection dialog box there is an option to allow users insert rows >> Check it
In the attached sample file (which is protected without password) You can edit any cell except the Yellow cells and if you INSERT ROWS in the middle of the range THEY INHERIT THE PROTECTION.
Hope that resolves your problem and you LIKE my answer
Good Luck
Nabil Mourad
Hi Nabil,
Attached is a slimmed down version of my spreadsheet showing my issue with inserting rows after the sheet is protected. The sheet is protected with no password. The formula I'd like to carry on is in Column R. After I follow the instructions listed above, protect the sheet, and insert a row, the formula cell in Column R doesn't copy. I've tried copying the formula formatting into the inserted row, but it doesn't work because the cells in Column R are locked for editing. This is the debacle I am currently facing. :)
Checking your sample file now Casey Mims
