formula dates in Pivot tables not grouping

MarkCBB

Active Member
Joined
Apr 12, 2010
Messages
497
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
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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

Active Member
Joined
Apr 12, 2010
Messages
497
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

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Glad I could help Mark. Your use of a dynamic range is a great approach for your application.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,755
Messages
5,626,671
Members
416,200
Latest member
Pulsar3000

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top