An Excel Structured Table Conundrum

[SkipVought]

Are you using Structured Tables in Excel 2007+... well are ya?

Well I am, and loving NEARLY every minute. But I've run into an opportunity.

I am essentialy creating my own pivot on dates. The ROW data is created using a query of my source data to get unique values. The COLUMN data is date buckets where the first column heading to the right of my querytable, is 0 (ZERO); second column heading is =TODAY(); third column heading and thereafter is =DATE(YEAR([PrevHeading]),MONTH([PrevHeading])+1,1) to display the first of each subsequent month.

So my summary data in the first column represents data prior to today. The second column represents data greater than of equal to today and less than the first of the next month. And so on.

The problem is that Structured Table HEADERS will not hold formulas. My solution up to this point, has been to separate the querytable from the Date headings and summary data below, by an empty column, do the date headings and then delete the empty column. This isolates the querytable from the date headings and summary data, which is the good news AND the bad news. I really want it all in one table, but it is not!

Has anyone encountered this issue? Any suggestions?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[genomon]

Not much help with Excel, just pointing out that MS Access may be a good option...

Beir bua agus beannacht!

 

[firefytr]

I know this is old, but I just saw it. Besides your workaround your only other option is VBA, which is a PITA in its own rite. You could use VBA to set the headers static from a set of formulas. You can return an ADO recordset to a table, just delete the data (DataBodyRange) first. Some examples of working with tables via VBA, not entirely related:

http://excelandaccess.wordpress.com/2013/01/07/working-with-tables-in-vba/

If you're not opposed to a VBA-based solution, it shouldn't be a problem.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP?
- Leonardo da Vinci