formula dates in Pivot tables not grouping

[MarkCBB]

Hi there, I am losing my mind.

I have a table that is a mirror on another table. the table is hidden from the user because they done need to see all the helper columns etc.

most of the formulas on the second (Mirror table) have the following formulas.
in cell A2(sheet2) =IF(Sheet1!A2="","",Sheet1!A2) however when I use a pivot table to do reports on the Mirror table I am not able to group the dates by month, dat, year etc. is there a setting I dont know about?

Thanks

 

[Jerry Sullivan]

Hi Mark,

My guess is that you have some Null string values mixed in with your date values in cells that your mirror formula returns "".

If you have a mix of dates and text values, then the pivot table won't give you the option to filter by dates.

This is probably happening if your Pivot Table source range extends beyond your last actual date value mirrored from Sheet1. If that's the case, then resizing your PT source range should fix this.

 

[MarkCBB]

Hi Jerry,

Yup looks like you are correct, even when I change the formula from =IF(Sheet1!A2="","",Sheet1!A2) to =sheet1!A2 I cant group the dates due to the null string values.

However I just figured out awat around it, I named the range of the mirror database with the OFFSET formula and used COUNTA to count the hieght of the Normal database, and thus define the group of dates on the Mirror database, so that way the Null strings are alway excluded.

Thanks for the insperation to that solution.

Regards,
Mark Blackburn

 

[Jerry Sullivan]

Glad I could help Mark. Your use of a dynamic range is a great approach for your application.