Excel Table with Formulae in Header Cells?

[cheesewoman]

Hi Everyone,

Sorry if this is a bit of a silly question but I'm a bit of a Noob when it comes to Excel Pivot tables etc, so please bear with me!

I am trying to create a pivot table based on the data from 3 sets of data.

To do this, I am first of all trying to create tables around my data sets, however; 2 of my tables have dynamic headings on the columns; they are dates which will change.. so Col1 heading =dateFromCell1, Col2 heading =dateFromCell2 etc etc. The number of columns will always remain the same (7), but the dates will change, and sometimes there will be a blank column if there are no entries for that particular day.

When I try to create an excel table using the above mentioned data range, it says that the headers will be converted to static text.

I understand that it does this to create references, but I need to be able to dynamically update those headings. So, is there some way to put formulae into table header cells, or to make table/pivot table heading cell values dynamic?

I can do a little VBA if needs be, but just struggling to figure out the best way around the issue...

Any help is much appreciated!

Cheesewoman

 

[Andrew Poulsom]

Welcome to MrExcel.

Can't you just have headers Day1, Day2 etc? Or use a range instead of a table.