5

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?

1
  • 2
    I think its static text only for the headers. You could use VB to overwrite the headers though.
    – James
    Jan 11, 2011 at 16:17

3 Answers 3

6

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.

2

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.

1

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .