Forum Discussion
Managing Conditional Formats
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.
mathetes That doesn't quite work in this usage case. I would make more sense if I could disclose the real data set but I can't really do that.
If we assume that each row represents a siding in a railway yard. I may have 50 sidings and have all fifty sidings showing in my table. I have three trains in sidings 1, 2 and 3 which are ten cars long. That's all good because these cars can accept ten cars.
But now each train has two cars added to the train and no longer fit in these sidings. They need to be en masse elsewhere. I can see from my table that sidings 21-23 are free as they do not have anything in the free text column. So I pick up my trains and move them there.
Unfortunately, it is not just a case of changing the lookup and resorting. There are actually three pairs of columns doing lookups and they are grouped as such because the allocations are all related. The action required literally is pick these values up and reallocate them to there - which is what every novice user would do in order not to break all the lovely formulas and calculations. The downside is it makes a mess of my conditional formatting manager!