Forum Discussion

Casey Mims's avatar
Casey Mims
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 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

 

Conditional Formatting
Excel 2016
inserting rows
Rules
spreadsheet
  • nabilmourad's avatar
    nabilmourad
    MVP
    Jul 12, 2019

    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

    • Casey Mims's avatar
      Casey Mims
      Copper Contributor
      Jul 12, 2019

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

       

       

    • Casey Mims's avatar
      Casey Mims
      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's avatar
        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

Resources