A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Pivot tables off a List



 
 
Thread Tools Display Modes
  #1  
Old August 21st, 2007, 04:18 PM posted to microsoft.public.excel.misc
Jonathan589
external usenet poster
 
Posts: 77
Default Pivot tables off a List

I’ve a big XL2003 table to which records are added every day or two, and a
couple of pivot tables analyse out what I want nicely, except …

I want counts and sums by month, where the column fields are the dates from
the records list. I want to group the dates into months. Following advice
from this forum I discovered that the table would not group my dates into
months if there were blanks, so it works if I just select populated rows.

But I have the source data set up as a List so that others can enter new
records. If I select the list including the new-entry row I get a blank. How
do I get this to let me group dates into months to show a constantly-evolving
table, yet allow new records to be added to the source data?

(I hope I'm not missing something simple and obvious!)
Ads
  #2  
Old August 23rd, 2007, 11:36 AM posted to microsoft.public.excel.misc
Jonathan589
external usenet poster
 
Posts: 77
Default Pivot tables off a List--solved

I solved it myself. Well, by no means alone, I used answers from this forum
and especially the source www.contextures.com.
I now know that if there are blanks in the list then grouping of dates into
months won't happen, and how to set up a named range that grows automatically
using the OFFSET function as shown in
www.contextures.com/xlPivot01.html#Dynamic.

My thanks to all

"Jonathan589" wrote:

I’ve a big XL2003 table to which records are added every day or two, and a
couple of pivot tables analyse out what I want nicely, except …

I want counts and sums by month, where the column fields are the dates from
the records list. I want to group the dates into months. Following advice
from this forum I discovered that the table would not group my dates into
months if there were blanks, so it works if I just select populated rows.

But I have the source data set up as a List so that others can enter new
records. If I select the list including the new-entry row I get a blank. How
do I get this to let me group dates into months to show a constantly-evolving
table, yet allow new records to be added to the source data?

(I hope I'm not missing something simple and obvious!)

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:38 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 OfficeFrustration.
The comments are property of their posters.