Hello all,
I'm having a very frustrating issue that has appeared in a few of my larger spreadsheets used as a central tracking mechanisms. I have written conditional formatting rules to change the color of rows in the spreadsheet based on data in a status column at the end of my spreadsheet. This spreadsheet is accessed by 7 people throughout the day with each person adding and editing rows in the table. My first issue was that the formatting would "break" at some point and I would have to go in to the conditional formatting rules and expand the applies to data to include the newly added rows. I thought I fixed this by created named ranges for each one of the rules. Because this data is in a table as you add a new row my named range is automatically expanded to incorporate that new data. Unfortunately, every week or so I have to go in to the conditional formatting and update the applies to and delete several instances of duplicate formulas that have been created (automatically) for single rows within the table. It doesn't take me long since I just delete all the duplicates and rename my applies to by the named range, but it's frustrating to have to do this weekly just to keep my spreadsheet clean and functioning.
Anyone have advice using conditional formatting for a dynamic table???
Jackie
I'm having a very frustrating issue that has appeared in a few of my larger spreadsheets used as a central tracking mechanisms. I have written conditional formatting rules to change the color of rows in the spreadsheet based on data in a status column at the end of my spreadsheet. This spreadsheet is accessed by 7 people throughout the day with each person adding and editing rows in the table. My first issue was that the formatting would "break" at some point and I would have to go in to the conditional formatting rules and expand the applies to data to include the newly added rows. I thought I fixed this by created named ranges for each one of the rules. Because this data is in a table as you add a new row my named range is automatically expanded to incorporate that new data. Unfortunately, every week or so I have to go in to the conditional formatting and update the applies to and delete several instances of duplicate formulas that have been created (automatically) for single rows within the table. It doesn't take me long since I just delete all the duplicates and rename my applies to by the named range, but it's frustrating to have to do this weekly just to keep my spreadsheet clean and functioning.
Anyone have advice using conditional formatting for a dynamic table???
Jackie