Conditional Formatting - how to minimize formatting rule duplication with editing

Copper Contributor

 

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? 

 

excel help 14.PNG

 

 

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?

 

excel help 15.PNG

 

Any guidance on these issues is greatly appreciated!!

 

Thanks,

Casey

 

8 Replies

@Casey Mims 

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

@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

 

Insert Rows.png

 

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

@nabilmourad 

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

@Casey Mims 

 

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

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