Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create mutli-level subtotals
HAve I confused two MS applications again? Working on spreadsheet with dates
in column "A". Column A is a standard date format, with dates formatterd as mm/dd/yyyy. Remaining columns have data to be subtotaled. I have set the subtotals to change each time the date changes. I get subtotals for each day(date) and a grand total. Here is the issue I am wrestling with; I would like to "automatically" get additional subtotals for WEEK, MONTH, QTR, and YEAR. I thought I had run across a way to do this some time ago, but have been unable to find the secret passage back there, now that I have the need. Or do I have to create additional columns and extract a Week, Month, Qtr, and Year from teh date column in order to make this work? I have been though MOST of the posts on subtotals without success. Any p[ointers would be greatly appreciated. Thanks in advance for the great support offered here. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create mutli-level subtotals
You could use a pivot table to summarize the data, and group the dates
by year, quarter, month, or days there. There are instructions and links he http://www.contextures.com/xlPivot01.html JR Hester wrote: HAve I confused two MS applications again? Working on spreadsheet with dates in column "A". Column A is a standard date format, with dates formatterd as mm/dd/yyyy. Remaining columns have data to be subtotaled. I have set the subtotals to change each time the date changes. I get subtotals for each day(date) and a grand total. Here is the issue I am wrestling with; I would like to "automatically" get additional subtotals for WEEK, MONTH, QTR, and YEAR. I thought I had run across a way to do this some time ago, but have been unable to find the secret passage back there, now that I have the need. Or do I have to create additional columns and extract a Week, Month, Qtr, and Year from teh date column in order to make this work? I have been though MOST of the posts on subtotals without success. Any p[ointers would be greatly appreciated. Thanks in advance for the great support offered here. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create mutli-level subtotals
Thanks for pointing me in that direction. I have not used pivot tables in the
past, and therefore am a bit of a novice with this feature. It took a few tries to find the date "week" definition your document referred to, but have it now. That has developed another question. The pivot table is report 8 "sums" for me. I also need to enter another 5 to 7 calculated fields. I successfully created two of these, however can't seem to get more than two calculated fields added to the data area. ? Is there a limit of 9 or 10 data elements that can be placed in a pivot table, or is there a limit to the number of calculated fields that can be placed in the data area? ? Question #2. How do I delete or remove a data element from the active pivot table? Thanks again "Debra Dalgleish" wrote: You could use a pivot table to summarize the data, and group the dates by year, quarter, month, or days there. There are instructions and links he http://www.contextures.com/xlPivot01.html JR Hester wrote: HAve I confused two MS applications again? Working on spreadsheet with dates in column "A". Column A is a standard date format, with dates formatterd as mm/dd/yyyy. Remaining columns have data to be subtotaled. I have set the subtotals to change each time the date changes. I get subtotals for each day(date) and a grand total. Here is the issue I am wrestling with; I would like to "automatically" get additional subtotals for WEEK, MONTH, QTR, and YEAR. I thought I had run across a way to do this some time ago, but have been unable to find the secret passage back there, now that I have the need. Or do I have to create additional columns and extract a Week, Month, Qtr, and Year from teh date column in order to make this work? I have been though MOST of the posts on subtotals without success. Any p[ointers would be greatly appreciated. Thanks in advance for the great support offered here. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create mutli-level subtotals
IS there a simple way to change the data range teh pivot table is based upon
after the table has been prepared? Thanks "Debra Dalgleish" wrote: You could use a pivot table to summarize the data, and group the dates by year, quarter, month, or days there. There are instructions and links he http://www.contextures.com/xlPivot01.html JR Hester wrote: HAve I confused two MS applications again? Working on spreadsheet with dates in column "A". Column A is a standard date format, with dates formatterd as mm/dd/yyyy. Remaining columns have data to be subtotaled. I have set the subtotals to change each time the date changes. I get subtotals for each day(date) and a grand total. Here is the issue I am wrestling with; I would like to "automatically" get additional subtotals for WEEK, MONTH, QTR, and YEAR. I thought I had run across a way to do this some time ago, but have been unable to find the secret passage back there, now that I have the need. Or do I have to create additional columns and extract a Week, Month, Qtr, and Year from teh date column in order to make this work? I have been though MOST of the posts on subtotals without success. Any p[ointers would be greatly appreciated. Thanks in advance for the great support offered here. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create mutli-level subtotals
The number of data elements may be limited by the number of columns
available. What happens when you try to create more calculated fields? To remove a data field, click the arrow on the Data field button, and remove the check mark for the data field. JR Hester wrote: Thanks for pointing me in that direction. I have not used pivot tables in the past, and therefore am a bit of a novice with this feature. It took a few tries to find the date "week" definition your document referred to, but have it now. That has developed another question. The pivot table is report 8 "sums" for me. I also need to enter another 5 to 7 calculated fields. I successfully created two of these, however can't seem to get more than two calculated fields added to the data area. ? Is there a limit of 9 or 10 data elements that can be placed in a pivot table, or is there a limit to the number of calculated fields that can be placed in the data area? ? Question #2. How do I delete or remove a data element from the active pivot table? Thanks again "Debra Dalgleish" wrote: You could use a pivot table to summarize the data, and group the dates by year, quarter, month, or days there. There are instructions and links he http://www.contextures.com/xlPivot01.html JR Hester wrote: HAve I confused two MS applications again? Working on spreadsheet with dates in column "A". Column A is a standard date format, with dates formatterd as mm/dd/yyyy. Remaining columns have data to be subtotaled. I have set the subtotals to change each time the date changes. I get subtotals for each day(date) and a grand total. Here is the issue I am wrestling with; I would like to "automatically" get additional subtotals for WEEK, MONTH, QTR, and YEAR. I thought I had run across a way to do this some time ago, but have been unable to find the secret passage back there, now that I have the need. Or do I have to create additional columns and extract a Week, Month, Qtr, and Year from teh date column in order to make this work? I have been though MOST of the posts on subtotals without success. Any p[ointers would be greatly appreciated. Thanks in advance for the great support offered here. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create mutli-level subtotals
Right-click a cell in the pivot table, and choose PivotTable Wizard.
Click the Back button Select the new range for the pivot table. Or if the range is changing because rows have been added, you could use a dynamic range as the source. There are instructions he http://www.contextures.com/xlPivot01.html JR Hester wrote: IS there a simple way to change the data range teh pivot table is based upon after the table has been prepared? Thanks "Debra Dalgleish" wrote: You could use a pivot table to summarize the data, and group the dates by year, quarter, month, or days there. There are instructions and links he http://www.contextures.com/xlPivot01.html JR Hester wrote: HAve I confused two MS applications again? Working on spreadsheet with dates in column "A". Column A is a standard date format, with dates formatterd as mm/dd/yyyy. Remaining columns have data to be subtotaled. I have set the subtotals to change each time the date changes. I get subtotals for each day(date) and a grand total. Here is the issue I am wrestling with; I would like to "automatically" get additional subtotals for WEEK, MONTH, QTR, and YEAR. I thought I had run across a way to do this some time ago, but have been unable to find the secret passage back there, now that I have the need. Or do I have to create additional columns and extract a Week, Month, Qtr, and Year from teh date column in order to make this work? I have been though MOST of the posts on subtotals without success. Any p[ointers would be greatly appreciated. Thanks in advance for the great support offered here. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create mutli-level subtotals
Thanks for getting back to me. After about 10 entries in the data section new
formula calculation fields were siomply placed on the field list. I had not noticed this when I fired off that question. I later noticed thses calculated fields and simply moved them to eth data area. Worked like a charm. I had not found a way to remove fields from teh pivot table so that was REALLY a help. Thanks. The reason I was wanting to redefine the data range, was that I had inappropriately included several blank rows in the range( to allow additional data entry). This naturally wreaked havoc on the regrouping of data into weeks or months etc, so I need to remove those empty rows. The pointer to the dynamic datasource was teh life saver too! That works great, because the spreadsheet I am developing for another department will grow continuously, at least until I can move them to Access„¢. This was my first real lif eexperience with Pivot Tables. Your responses and your CONTEXTURES link saved the day. A 1000 thanks "Debra Dalgleish" wrote: The number of data elements may be limited by the number of columns available. What happens when you try to create more calculated fields? To remove a data field, click the arrow on the Data field button, and remove the check mark for the data field. JR Hester wrote: Thanks for pointing me in that direction. I have not used pivot tables in the past, and therefore am a bit of a novice with this feature. It took a few tries to find the date "week" definition your document referred to, but have it now. That has developed another question. The pivot table is report 8 "sums" for me. I also need to enter another 5 to 7 calculated fields. I successfully created two of these, however can't seem to get more than two calculated fields added to the data area. ? Is there a limit of 9 or 10 data elements that can be placed in a pivot table, or is there a limit to the number of calculated fields that can be placed in the data area? ? Question #2. How do I delete or remove a data element from the active pivot table? Thanks again "Debra Dalgleish" wrote: You could use a pivot table to summarize the data, and group the dates by year, quarter, month, or days there. There are instructions and links he http://www.contextures.com/xlPivot01.html JR Hester wrote: HAve I confused two MS applications again? Working on spreadsheet with dates in column "A". Column A is a standard date format, with dates formatterd as mm/dd/yyyy. Remaining columns have data to be subtotaled. I have set the subtotals to change each time the date changes. I get subtotals for each day(date) and a grand total. Here is the issue I am wrestling with; I would like to "automatically" get additional subtotals for WEEK, MONTH, QTR, and YEAR. I thought I had run across a way to do this some time ago, but have been unable to find the secret passage back there, now that I have the need. Or do I have to create additional columns and extract a Week, Month, Qtr, and Year from teh date column in order to make this work? I have been though MOST of the posts on subtotals without success. Any p[ointers would be greatly appreciated. Thanks in advance for the great support offered here. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Create mutli-level subtotals
You're welcome! Thanks for letting me know that the information helped you.
JR Hester wrote: Thanks for getting back to me. After about 10 entries in the data section new formula calculation fields were siomply placed on the field list. I had not noticed this when I fired off that question. I later noticed thses calculated fields and simply moved them to eth data area. Worked like a charm. I had not found a way to remove fields from teh pivot table so that was REALLY a help. Thanks. The reason I was wanting to redefine the data range, was that I had inappropriately included several blank rows in the range( to allow additional data entry). This naturally wreaked havoc on the regrouping of data into weeks or months etc, so I need to remove those empty rows. The pointer to the dynamic datasource was teh life saver too! That works great, because the spreadsheet I am developing for another department will grow continuously, at least until I can move them to Access„¢. This was my first real lif eexperience with Pivot Tables. Your responses and your CONTEXTURES link saved the day. A 1000 thanks "Debra Dalgleish" wrote: The number of data elements may be limited by the number of columns available. What happens when you try to create more calculated fields? To remove a data field, click the arrow on the Data field button, and remove the check mark for the data field. JR Hester wrote: Thanks for pointing me in that direction. I have not used pivot tables in the past, and therefore am a bit of a novice with this feature. It took a few tries to find the date "week" definition your document referred to, but have it now. That has developed another question. The pivot table is report 8 "sums" for me. I also need to enter another 5 to 7 calculated fields. I successfully created two of these, however can't seem to get more than two calculated fields added to the data area. ? Is there a limit of 9 or 10 data elements that can be placed in a pivot table, or is there a limit to the number of calculated fields that can be placed in the data area? ? Question #2. How do I delete or remove a data element from the active pivot table? Thanks again "Debra Dalgleish" wrote: You could use a pivot table to summarize the data, and group the dates by year, quarter, month, or days there. There are instructions and links he http://www.contextures.com/xlPivot01.html JR Hester wrote: HAve I confused two MS applications again? Working on spreadsheet with dates in column "A". Column A is a standard date format, with dates formatterd as mm/dd/yyyy. Remaining columns have data to be subtotaled. I have set the subtotals to change each time the date changes. I get subtotals for each day(date) and a grand total. Here is the issue I am wrestling with; I would like to "automatically" get additional subtotals for WEEK, MONTH, QTR, and YEAR. I thought I had run across a way to do this some time ago, but have been unable to find the secret passage back there, now that I have the need. Or do I have to create additional columns and extract a Week, Month, Qtr, and Year from teh date column in order to make this work? I have been though MOST of the posts on subtotals without success. Any p[ointers would be greatly appreciated. Thanks in advance for the great support offered here. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I copy and paste a formula I create to subtotals only? | Excel Worksheet Functions | |||
Subtotals to separate sheets? | Excel Discussion (Misc queries) | |||
How i can create a formula that combine subtotals and sumif | Excel Worksheet Functions | |||
Create a tabular control | Excel Discussion (Misc queries) | |||
Subtotals multiple columns | Excel Worksheet Functions |