Jul 11 2019 11:17 AM
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 contains several conditional formatting rules. When I turn it over for use, the end user has to insert rows in the table at times as new projects come in. I think this is causing the conditional formatting rules to explode. As a result, I have lots of rules that pertain to only one or a few cells, instead of the entire range as initially created. Is there a way to prevent this from happening?
2. The Projected columns contain rather complicated nested formulas for projecting due dates. I've asked the end user to NOT manually enter values into these columns, as it erases my formula, but with little luck. Is there a way to protect the integrity of these cells while allowing the end user to still add/remove rows from the table?
Any guidance on these issues is greatly appreciated!!
Thanks,
Casey
Jul 11 2019 07:36 PM
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
Jul 12 2019 06:30 AM
@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
Jul 12 2019 06:50 AM
Hi Casey,
Glad the video helped.
The situation is:
In any Excel spreadsheet we have 17,179,869,184 cells
All 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
Jul 12 2019 07:12 AM
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. :)
Jul 12 2019 07:30 AM
Checking your sample file now @Casey Mims
Jul 12 2019 07:40 AM
I see what you mean my friend.
The solution is:
For column R >> in the Protection Tab of the Format cell Dialog box>> Uncheck "Locked" and Check "Hidden"
Hope that helps
Let me know to celebrate the success :)
Nabil mourad
Jul 12 2019 07:43 AM
I see what you mean my friend.
The solution is:
For column R >> in the Protection Tab of the Format cell Dialog box>> Uncheck "Locked" and Check "Hidden"
Hope that helps
Let me know to celebrate the success :)
Nabil Mourad
Jul 12 2019 10:11 AM
Hi Nabil, unfortunately this does not work, since unlocking the column then allows it to be written over by the end user. I will table this issue for now. I would still like help with the conditional formatting issue, if you are interested in trying to address that one. :)