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.

Excel Do, Dynamic Does : CF, Servertructured References

Who Scratched My Table?

Formatting The Table

Using Structured References

Conclusion

Summary