Who Scratched My Table?

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.


 

8 thoughts on “Who Scratched My Table?”

  1. Bob,
    You cannot use Table Names in many places.
    Eg. Data Validation – List Option
    So tblShipping[Customers] would not work
    The work around is to first create a name
    Client =tblShipping[Customer]
    And use Client in the Data Validation list option
    The same for CF

    Which makes you wonder why bother – use Dynamic names instead.
    Tables and Structured reference are both half baked features…still some way to go…

  2. One more work around is to use the indirect function.

    Data Validation – List Option

    INDIRECT(“tblShipping[Customer]“)

    Regards

  3. Elias has it right. Except add the ‘@’ to refer to the current row of the column. Try this in the conditional formatting entry:

    =INDIRECT(“tblShipping[@Customer]“)=”TBA”

    If it’s in a separate workbook:

    =INDIRECT(“fileName.xlsx!tblShipping[@Customer]“)=”TBA”

  4. Paul,

    Thank you SO much for nailing a solution to this problem! Your contribution is greatly appreciated. I can now use conditional formatting inside of a structured table.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>