Excel Do, Dynamic Does : CF, 2007

Who Scratched My Table?

Bob Phillips — Sun, 11 Jul 2010 10:45:00 GMT

One of the better additions to Excel 2007 are, in my view, the functionality added to tables http://www.jkp-ads.com/articles/Excel2007tables.asp.

I was recently working on a 2007 project and I was looking to add some highlighting using conditional formatting. That will be easy thought I, but as usual, I was way off the mark.

Formatting The Table

For the sake of clarity, I will not use the actual formatting that I was trying, but a far simpler case.

Figure 1 shows an example of a typical table in Excel 2007.

Figure 1

Let’s assume that I want to show those rows where the Ship Date has not yet been set, i.e. are showing TBA.

Using conditional formatting, I can easily do that by selecting B2:E8, and then adding a formula of

=$F2=”TBA”

and adding an appropriate fill colour.

To demonstrate this, Figure 2 shows the formatted table.

Figure 2

Using Structured References

But hey, this data is in a table, and one of the great features of tables is structured references. So I decided that I would use structured references in my conditional formatting.

As a demonstration of structured references, Figure 3 shows the table with my CF formula off-table.

Figure 3

As you can see, you reference the table, names tblShipping here, and the column, not each individual cell.

So clearly, I just have to replace the first CF formula with cell references with this new formula with structured references, Figure 4.

That is what I did, and to my utter surprise, I got the following error.

Figure 4

As an aside, I did click Help, but I won’t bore you with the details on the lack of help that provided, just suffice to say that it was to Excel 2007’s usual standard.

But what can the error be? There is nothing wrong with the formula as Figure 3 shows.

One thing did occur to me. My testing formula was not part of the table, it was outwith the table, whereas CF is part of the table (perhaps, who knows?). If you add that formula to the column next to the table, the table expands to encompass that column, Figure 5.

Figure 5

Although I used exactly the same formula as before, when the table incorporated the column with that formula, it dropped the table reference, clearly it is superfluous, or implicit.

Armed with all of this, I decided to try that version of the formula in my CF. I still got an error, but a different error

Figure 6

Conclusion

This suggests to me that and formatting added by conditional formatting is not ‘part of the table’. And because it is part of the table, it does not work, you cannot use structured references in CF.

I tried a few variations of the formula, mainly in desperation, but none of these worked either. One interesting aside, when I tried to use a formula with an explicit intersection within the formula embedded in the table, it didn’t strip off the table name, I had to use

tblShipping[[#This Row],[Ship Date]])=”TBA”

Yet another oddity.

Summary

I have written before about the implementation of conditional formatting in Excel 2007, in Conditional Formatting In Excel 2007 – Chav or Mini?, and Open Office Is Looking Good. From these posts, you should be clear that I am far from impressed with conditional formatting in Excel 2007, and unfortunately this looks like another piece of poor implementation.

It seems that you can conditionally format a table in Excel 2007, but you cannot use structured references in that CF. Okay, so you can use normal CF, but big deal! Why can’t we use structured references when applying CF to a table? To my mind, not being able to is a total nonsense, another CF car crash piling up on the others.

I did also try and use a structured reference in CF on cells not in the table, still no joy.

If anyone knows how structured reference can be, should be, used, I would love to hear it.

OpenOffice Is Looking Good

Bob Phillips — Mon, 09 Nov 2009 12:45:00 GMT

Normal 0 false false false MicrosoftInternetExplorer4

I have blogged previously about what a rubbish job MS have done with Conditional Formatting in Excel 2007, but it seems it is worse than I feared.

In this same file I now had CF setup as I wanted, after much trials and tribulations, but I needed to insert a new column, but the CF was required to be different than the CF on either side.

First thing I tried was to simply insert the column. Of course, this meant that it inherited the CF from the column to the left, which was not what I wanted, so I opened the CF dialog and thought I would change the range that the rule applies to from those two columns to just my newly inserted column, and change that rule. This I did, but it wiped out the rule entirely from the other column. And to compound the problem, it cleared the format fill colour from the newly inserted column, and when I changed the rule and added back the fill colour, every cell in my range showed the colour, even though only a single cell matched the condition being tested for.

So I tried another approach. I created a column off the data area, setup the CF exactly as I wanted for that column, then added an index number in the first free row so that I could sort the columns into my required order. For some unknown reason, this removed my CF, gave it the CF of the column to its (new) left, and also managed to setup data validation as was applied to its (new)O column to the right. Genius! I knew what I wanted to do, but the designers at MS decide they know better, in two ways.

So how did I fix it? I first deleted the CF from the column that is already present, then inserted my new column. I then re-instated the CF that was in the previous column, and added the CF in my new column. Not a great chore, but I SHOULDN’T HAVE TO FIGHT THE SOFTWARE to do what I want to do. And you can bet that the next time I need to do it, I will have forgotten, and I will go through all this pain again.

Whilst I am having a good rant, when are MS going to default CF (and Data Validation) edit boxes to update, so as to avoid accidentally inserting rubbish when you hit the arrow keys. I can’t recall ever thinking the default was useful.

Conditional Formatting In Excel 2007 - Chav or Mini?

Bob Phillips — Tue, 08 Sep 2009 08:06:00 GMT

Yesterday I had an incredibly frustrating time.

I was updating my RibbonX Autogen routine to handle more types of controls. This is driven from a worksheet, some columns of which are generated, some of which depend upon user input. So being a helpful sort of guy, I have decided to shade the input cells as a visual aid. But in some circumstances, an input cell may not be required (for instance, a group does not require an image, so suggesting that it does by shading is a tad misleading). Being the helpful sort that I alluded to earlier, I decided that I would use conditional formatting, to test those conditions, and remove the shading where appropriate. Which is where I came up against the MS designers, who seem to me to live in a land far from reality, totally unaware of what people really do.

Okay, we all know that conditional formatting was limited, 3 conditions was never enough, and that the CF dialog was rubbish, but why didn't they just address those issues? Why re-design the whole way it works? Don't MS realise that evolution is just as valid an approach as revolution, change the things that really requirie changing first, see how it gets used, and then gradually build upon that improve. In other words, let the real users have some influence.

The first problem that I came across was the new behaviour whereby if you create a condition, and then paste the format of another cell over the top, another cell that also has CF, the target cell adds those format conditions. No options, you can't say you don't want to add, it just does it. Of course, unless you are aware of this, you may find your cells reacting to a differenet condition, and you then start wondering why the CF no longer works.

Next, I added some CF conditions to a number of rows over 2 columns. That was fine until I decided that I wanted one of the columns to have an extra test in the condition. So I selected the cells in that column, and edited the rule to include the new test. Lo and behold, the other column also adopted these tests. When you set conditional formatting on a range, Excel remembers which cells are using that rule, and so change the rule in any cell and they all get the change. Again, no choice that I can see, I can't tell it what I want, I get what MS decide because MS knows best. In the end the only solution that I could find was by deleting the formatting on both columns and then recreating singly on each.

And of course, when you are in a cell that was part of a range that has been conditionally formatted, the formula is no longer adjusted to that cell as it was it previous versions of Excel, it shows the actual formula you applied. So I am in P15, and in the rule I see the formula =OR($A3="break",$B3,AND($A3="",$M3<>"")). The first time I encountered this it really threw me. What possible purpose can this change serve? I cannot see it.

Add to all of this rubbish the new, glitzy, childish icon sets, the fact that the formula box still defaults to input (so you have to F2 to actually use it), the fact that the dialog is not resizeable in order to better view your formula; I can only conclude that this is another very bad user dialog.

Considering that Excel 2007 was supposed to be simpler to use, and remove some of the mysteries of Excel, I think the designers of Conditional Formatting in Excel 2007 have failed abysmally. As seems to be the way with MS and Excel, too much effort was spent on adding unnecessary and distracting glitz, and very little on real usability.