Formula in table header cells

I have a table in Excel 2007 that I want to summarise, in a similar fashion to a Pivot Table, but for various reasons I can't use a pivot table.

I like the "Format as table" features of sort and filter buttons, automatic formatting etc, so have used that to create a simple table:

         A            B            C                    N 
   +-----------+------------+------------+-------+------------+
 1 |           | 01/01/2010 | 01/02/2010 |  ...  | 01/12/2010 |
   +-----------+------------+------------+-------+------------+
 2 | CategoryA |     15     |     545    |       |    634     |
 3 | CategoryB |     32     |     332    |       |    231     |
 4 | CategoryC |      5     |     234    |       |    644     |
   |    ...    |            |            |       |            |
27 | CategoryZ |      2     |     123    |       |     64     |
   +-----------+------------+------------+-------+------------+

The numbers are retrieved from a "back-end" pivot table using GETPIVOTDATA().

All that works fine.

Now, the problem is that I can't seem to use formulas for my column headings in these new "smart" tables - they are converted to text or just broken.

For example if in B1 I put NOW(), I don't get the date, I get 00/01/1900.

Is there any way of getting a formula to work in the auto tables? Or do I have to use standard tables and manually alternate-colour my rows etc?

It is only static text for headers because they are named fields and therefore can be referenced. If they were dynamic, it would wreak havoc with formulas using them as references.

I am using Excel 2010, but this may work in 2007. If you turn off the Header Row in the Table Design, you should be able use a formula in the row above your table data.

If you use Table, Convert to Range it should keep the colouration of alternate rows but still allow formulae in the column headings.