Formula in table header cells

Question

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?

Answer 1

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.