Pivot Table won't Group Dates from growing Table

[BrerRabbit]

I have a table that is solely populated by formulae. Over the course of time more data will populate this table via the formulae.

The Date column does use the DateValue formula.

I'm trying to do a pivot table off this table.
If I do a pivot table where the range from the table is just the data then I can group the dates. BUT, the pivot table won't include new data.
If I do a pivot table where the range includes blank rows (only has formula in them) then the dates won't group bcuz there is a blank row included in the pivot table.

I don't know how to proceed.

 

[RoryA]

If your data is in a Table, it shouldn't have blank rows in it. Half the point of using a table is that it automatically expands as you add new data to it.

 

[BrerRabbit]

If your data is in a Table, it shouldn't have blank rows in it. Half the point of using a table is that it automatically expands as you add new data to it.

the table is populated by formula coming from another worksheet. hence the empty rows of formula. I don't know how large the table will ultimately be. It collects data over the year.

 

[RoryA]

You should still only add new rows as and when necessary. If you don't, and you can't put a valid default date in that column, then your pivot table won't be able to group, so you'd have to include the groups in the source table.

 

[jkpieterse]

Why do you need an intermediate table with formulas to get data for a pivot table?

 

[BrerRabbit]

Why do you need an intermediate table with formulas to get data for a pivot table?

It's an unusual project where I've had to do alot of thinking outside the box. I've essentially had to break down a very basic 4 week per month calendar into a single column and then analyse the data from that column. over 90,000 rows long. The table is a list of first instances from that column.

Bcuz the "calendar" events that were being recorded meant that each cell had to have 3 bits of information instead of one, the table expands those 3 bits of information into useable data. Hence why the table is all formulae. Plus, it's usable over 5 years. So the table is never going to be an exact fit to the populated data.

I may just have to do the pivot tables as normal tables and do them the "long" way.

 

[BrerRabbit]

You should still only add new rows as and when necessary. If you don't, and you can't put a valid default date in that column, then your pivot table won't be able to group, so you'd have to include the groups in the source table.

I don't understand "include the groups in the source table". Can you please explain what you mean by that?

 

[RoryA]

I mean add columns to the table that extract whatever date groupings you need - e.g. using the YEAR, MONTH, or TEXT functions.

 

[jkpieterse]

You can probably use Data, From Table to massage your original data into the same structure you currently have for the Pivot table. The result of that automagically is a table and hence no issues with grouping.

 

[BrerRabbit]

I mean add columns to the table that extract whatever date groupings you need - e.g. using the YEAR, MONTH, or TEXT functions.

Actually thats an intriguing thought. Why would I do that and how would that be used in a pivot table? I understand formulae VERY well, but am still very new to pivot tables. In this project pivot tables could not be utilised until the very last step. Even Excel popped up a message saying I couldn't use pivot tables in this project.

Can you describe how I would incorporate those groupings into a pivot table?