Forum Discussion

Copper Contributor

Jul 11, 2019

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...

Tracker_condFormissue.xlsx120 KB

Conditional Formatting

Copper Contributor

Jul 12, 2019

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

nabilmourad

MVP

Jul 12, 2019

Casey Mims

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