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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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!) |
#2
|
|||
|
|||
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 | |
|
|