Pivot table will not group date

E

EXCEL DODO

Board Regular
Joined
Aug 21, 2003
Messages
79
Hi

I am challenged/very frustrated with my pivot table which will not group dates (returns an error message).

In reading other posted questions, I see that my problem is caused by empty date cells. Is there a way around this?

My spreadsheet will be used daily to input Invoice details - part of which is the date of invoice. If I were to select 'refresh data', this would not be pulling through any new data as it wouldn't be in the range. I had made my range A1:K1500 to incorporate future entries, but then I have empty date cells as to date there are only 200 entries.

Hope this makes sense!! Would really appreciate any suggestions.

Many thanks
Lyn
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
EXCEL DODO,

Try making a dynamic range name for the data and use the name in the Pivot Table.

Dufus
 
Upvote 0
Thanks Dufus and Brian

Yes I am using 2003. How do I use a name in a pivot table. I have selected my column (B), then Insert|Name|Define and named it Invoice_date. How do I now pull that through to my pivot table fields. Sorry...I am new to pivot tables and it is driving me to drink!!

Another one you may be able to help me with. I have hidden a sheet by select Alt+F11, locating the sheet and changing the Visible to xlveryhidden. Now I can't get it back!! When I select Alt+F11, I can't even find anything. Sorry...not very good at VBA.....but enjoying the learning and really appreciate your valuable knowledge and assistance.

Cheers
Lyn
 
Upvote 0
Assuming your data range is in Sheet1, click on Insert menu, Name, Define and in the "Names in workbook", assign a name e.g. DATABASE, and in the "Refers to" box, enter the below formula:

=Sheet1!$A$1:INDEX(Sheet1!$K$1:$K$65535,MATCH(TRUE,ISBLANK(Sheet1!$A$1:$A$65535),0)-1)

Now in your pivot table data range, instead of a range, simply enter the word "DATABASE" and proceed with your pivot table wizard.
 
Upvote 0
Regarding your second issue - when you're in the VBA window, is everything associated with this workbook's file fully opened?

(click on the plus sign inside the box)

You should see something like a bold: "VBA Project (your_file_name_here...)

Inside that, you should have "Microsoft Excel Objects",

and inside THAT, you should find the worksheet you've hidden.
 
Upvote 0
Thanks for suggestion. I entered formula and in my pivot table creation I got "the command requires at least 2 rows of source data. You cannot use the command on a selection in only one row...etc". My sheet is called "Invoices" and my data now includes to column U. I adjusted your formula to:


=Invoices!$A$1:INDEX(Invoices!$U$1:$U$65535,MATCH(TRUE,ISBLANK(Invoices!$A$1:$A$65535),0)-1)

Where have I gone wrong?

Thanks
Lyn
 
Upvote 0
Thanks NJEddie, managed to unhide my sheet.
:biggrin:

Just need to resolve the pivot table date problem now????

Lyn
 
Upvote 0
I'm not familiar with this particular issue, but couldn't you take a cell with a zero in it, and do a "Copy, Paste Special, Value/Add" to put a zero into the cells that had been blank? Would that fix the problem?
 
Upvote 0
I'm not familiar with this particular issue, but couldn't you take a cell with a zero in it, and do a "Copy, Paste Special, Value/Add" to put a zero into the cells that had been blank? Would that fix the problem?
Click to expand...

That certainly will fix the problem (until you Refresh anyhow). It will mean that you will have an odd date of 00 January 1900 for those without dates currently, but as long as you keep in mind these are dateless entries I don't think it should be a problem.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
I Can Only Group Dates by Selecting all Cells, not Single ones. Dialogue Box Won't Pop Up
Replies
1
Views
576
arthurbr
arthurbr
B
  • Question
Update Pivot Tables with Refresh
Replies
1
Views
140
shift-del
shift-del
M
  • Question
Excel pivot table query - retaining filters on pivot in absence of data
2
Replies
10
Views
1K
Alex Blakenburg
A
C
  • Question
Confused over pivot table calculations :-(
Replies
1
Views
258
number kruncher
N
M
  • Question
VBA Code to copy rows by date from one tab to another / repeat based on new date
Replies
3
Views
231
kevin9999
K

Forum statistics

Threads
1,214,979
Messages
6,122,557
Members
449,088
Latest member
davidcom

Share this page

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
Back
Top