-2

So I want to create a Pivot Table from some data in Excel.

In the first column I have Dates in format dd/mm/yyyy and the other 4 columns are text.

I have a dynamic range called DynamicData that refers to all the data (it gets it well).

The problem is :

When I insert the Pivot Table Range manually selecting it, the Dates are well taken.

When I use the Defined name the dates are not well interpreted by the pivot table and I cannot group by month.

Dynamic defined names in pivot tables do not work well for dates???

Thank you all in advance

manel
  • 1
  • you can use a dynamic named range as a pivottable source. Can you show us some data and an example of what you are talking about? – QHarr Feb 21 '18 at 19:53
  • Be aware that in later versions of Excel there will be an automatic grouping of dates within pivots that you then have to right click and ungroup to return to more a more granular level. – QHarr Feb 21 '18 at 20:26
  • Yes I was using the dynamic range for the pivottable source and the range was correct, but the pivottable wouldn't recognize the dates. It got it all as it was text. Changing to a static range and resizing it with vba sovled the problem – manel Feb 22 '18 at 10:56

1 Answers1

0

Alright I found the solution:

Apparently what was causing the issue was the formula in the defined name (OFFSET formula).

Solution : setting a defined range for the defined name (=Aux!$A$1:$E$2256) instead of an OFFSET formula. I then resize the defined name range depending on the data using vba. I followed this link : https://www.extendoffice.com/documents/excel/2894-excel-resize-named-range.html

Now the Pivot Tables made from the data do recognize the dates. Quite a strange issue, thank you all for your answers.

manel
  • 1