Forum Discussion
Managing Conditional Formats
I'm trying, but having a difficult time, to visualize what you might be working with here. Fundamentally, I'm wondering if part of the difficulty here is in the way you are approaching the basic organization of your data. If you are, as you suggest, moving and copying data around the spreadsheets, that in and of itself may actually be an unnecessary and ineffective way to approach whatever the basic task is.
Is it in any way possible for you to share a copy of your actual spreadsheet here? If it contains, as well it might, confidential information, then create a fictional version that still resembles it, using fake names of whatever it is that is confidential.
If that's not possible, then a far more complete description of the bigger picture: what's the context, what is the nature of the core data that is entered (the Input); what is the expected or desired result (the Output); what is it that gets changed, by whom, how frequently.
Your conditional formatting problems may just be the visible symptoms of a far deeper issue of database design. Perhaps it started as an Excel representation of a process developed on paper, I don't know. That does happen, and such a developmental process sometimes fails to take advantage of Excel's inherent abilities to manipulate data on its own, without requiring you to "move and copy"....
mathetes I have created an example which I think includes all the functionality I am having trouble with.
As I add new rows to the table in Sheet1, (new "Value", leading to a valid lookup from the names, a random entry under "Description" and then picking a category from the valid list) it seems to work, with the "Applies to" formula in Conditional Formatting dialog box expanding to include those new rows.
So I'm not clear on the problem/concern you describe in this statement in cell F7 of Sheet 1:
Values in named range may change therefore breaking conditional formatting
- Which of your several named ranges is the "named range" to which you refer in that statement?
- Why would that named range change? What would be the nature of the change?
By the way, why are the entries under "Description" free form as opposed to subject to Data Validation themselves. If we stay with this as tables/zones (in a restaurant?), it's surely a finite list; it could be made subject to data validation and avoiding spelling errors, etc.
What do the Red, Blue, Green and Yellow "categories" designate in relationship to the other columns. Are there rules that could be written to make that selection itself formula based? This person to this table always = Green, that kind of thing? Or are the category designations always random?
mathetes The data I have used is random. The name column is the important bit of information. The description is very much ad-hoc and is free-text entered on the day depending on the requirements. Data validation could be applied, but this would be too restrictive and frustrating to users.
The category from the last row is simply used as a way to highlight that row in a certain colour. For example, everything that is category green stands out as such.
In the attached example I have shown where a user as quickly added some extra rows, auto-filled the value, and copy and pasted the descriptions and categories from above. This is because where those allocations were have now been moved to a different line. As you can see, the formatting has not been applied to the lookups for the newly created rows. The conditional formatting manager manager now has extra rules in it for cell references where values have been moved around. If the rules could be applied to the table, instead of cell references, this would not be an issue. Just the window being bigger would help!
Is there a reason for doing the copying and pasting in the middle of the existing table?
I did a couple of copy-paste adding to the bottom of the table; I then sorted the new, extended table based on the values, and as you can see in the Conditional Formatting rules management screen, the cells to which the conditional rules applied just got extended.
So if you can change the procedure by which rows get copied and pasted, so they all get added to the bottom instead of in the middle, then sort them (if that's necessary; you might have to add a new column as the basis for sorting)...
For what it's worth, I have experienced the same problem that you're complaining about here in a spreadsheet of my own, on a summary sheet that tracks options investments, where I'll insert a new row for a new investment into the middle of that summary table. Now I'm going to go see if I can add my new rows at the bottom and then just re-sort the rows. It wasn't a big enough problem for me to have to fret, but if this works.
Now, there may indeed be a way to be even more sophisticated, and I'll keep looking; maybe somebody else can chime in who already has a better answer. In the meantime, though, try just adding those copy/paste rows at the bottom of your existing table.