I want to create a summary pivot table report + chart. What I have is 12 worksheets, one for each month, each with total amount invoiced and gross profit by client.
I want a pivot table that shows me the clients and amounts each month (this is the bit stumping me). Basically I want a drop down box to select the month on the pivot table, which then shows the data from the relevant worksheet (Aug-09, Sep-09 or whatever).
I've tried using multiple consolidation ranges but this just aggregates all the data; it doesn't allow me to limit by the worksheet the data has come from.
I'm wondering if I have to consolidate all my months into one big sheet, then add a field for the month. I would rather avoid this but accept it might be the only way.
Final thing; can't use VBA at all (too much hassle for IT apparently).
How can I get a pivot table to calculate the average of the monthly totals and not the average of all the raw data.
Below is a pivot table of the data on the left. When the pivot table averages the months it calculates the average of every single record, 48.83. How can I get it to average the monthly totals:average(apr,may,Jun)= 179.05
Date cost Sum of cost Years Date 4/1/2011 -75.00 2011
2011 Average Monthly Average 4/11/2011 -58.88 Apr May Jun
I have an expense ledger that looks essentially like this but spans seven months and counting:
HTML 18-Apr-08pizza$10.00Food 14-Apr-08book$12.50School 13-Apr-08milk $3.99Food 13-Apr-08soap $3.99Grooming I have been using pivot tables with great success to summarize my data according to categories, payment methods, etc. But now that I want to track monthly spending for each category, it is not working out. Grouping the date fields doesn't work for me for the following reasons: 1. After grouping, the pivot table treats Jan as the oldest month for sorting purposes even though it isn't so in my data set. 2. Based on #1, I believe that the pivot table’s monthly grouping works like the month() function, which returns the month without the year attached. This will not work for my data, which will likely span more than a year eventually. 3. When I choose to group the date fields in one pivot table, they also get grouped in other tables where such grouping is undesirable. Can I use sumif() or sumproduct() in conjunction with a pivot table or some other way to tabulate my monthly totals?
I'm using Excel 2007 and am having an issue with grouping/ungrouping fields in pivot tables.
I have 2 separate pivot tables, both from the same named data source, but summarizing different data selections. Both tables include the date field, I am trying to produce both a daily and a monthly table, but whenever I change the grouping/ungrouping of the date field setting on one table, the other table changes to the same grouping.
Is there anyway to have one table with an ungrouped date field and one table with the grouped to month date field?
I have a table of yearly totals for the amount spent by x. I also have a growth rate for each month so for example in 2001 in jan the growth rate might have been 0.3% and feb 0.5% What I want to do is for each month based on the growth rate and the total produce a value for each month which sum to the total amount. It's also important to note that it restarts each year.
I have two worksheets. One shows imports, the other sales.
I might import Item A at a price of $1.00 and a few months later Item A costs $1.25. I got 100 at $1.00 and 40 at $1.25.
I have sold a total of 110 items.
What I want to do is make a pivot table from both worksheets. I want it to show that I imported 140 units of Item A at a total cost of $150.00 yet I have only spent $112.50. My arrangement with the source vendor is to pay AFTER the product is sold. So I owe $112.50 to the source vendor.
I've tried copy and paste of one sheet into a 3rd new sheet and then copy and paste the common information PLUS Quantity Sold from the 2nd sheet into the 3rd.
When I make the pivot table, and I can not get Item A to multiply 100 x $1.00 and 10 x $1.25. It shows sales of 100 and sales of 10. I can get it to show costs of $1.00 and $1.25. But I can not get it to multiply, as the figures are one two different lines.
Is there a way to create a pivot from multiple external worksheets. I have the same format and field names in different folders on our network. I am proabably looking at reading from 5 different files. Is it an option to write a query with UNION ALL?
I have a spreadsheet with data across multiple worksheets and I am trying to figure out how to use the data from the multiple spreadsheets to create a single pivot table on a separate worksheet. I cannot reasonably bring all the data into one workshet because each one has thousands of rows. I want to pull data from select columns in each worksheet. How can I isolate only the data I need from different worksheets into one pivot table?
I believe in the past that I have told Excel to send the output of the pivot table to different worksheets. For example if I have three regions- the pivot table would send the output to three worksheets and name the worksheets with the three regions.
I cannot solve with Excel 2010 and I have searched all over for the answer.
I have sales data that is approximately daily and would like to count the monthly data and summarize it as an average for the month in a separate column.
I am using Excel 2007. I have a population that I used to create a pivot table. I am currently double clicking on the value cells to create worksheets of only particular "row label" categories. I am then copying the "row label" information into the newly generated work sheet name tab. This works fine when I only have a few "row label" categories to do but it is tedious if there are many categories.
Is there any way to automate the creation of work sheets for all row label values and also naming each work sheet tab with it's respective row label information. Here are images of the pibot table and the type of work sheet I would like for wall row label values.
I upgraded to Excel 2010 from Excel 2000 a couple months ago. In Excel 2010, after double-clicking on values in a pivot table to create a new worksheets, I'm unable to highlight those worksheets and add a column to all of them. I was able to do this in Excel 2000... I can't figure out what's stopping it from happening for the life of me. Am I missing something obvious here?
- a list of data - a pivottable based on these data
When moving this worksheet this worksheet to another workbook, the pivot table can't refresh anymore. This throws an error message "Reference is not valid". To work around this problem I need to adapt the datasource. The same occurs if the list and the pivot table are on separate sheet, with the added strange behaviour that, when data an PT are split, it is not possible to move both sheet together.
This would not be a big issue if my problem had to be solved manually. The real problem is that I need to move the sheets from a C# program.
I am trying to compare almost 2 identical spreadsheets. Please see the attachment below. This is used by our HR department to identify people that are active or inactive, department, title, who are their supervisors, etc. This spreadsheet will be updated monthly to reflect all the changes (new employees, people move from one dept/home location) to another, come back from temporary leave, promoted, or terminated). I saw some macro code application to highlight the differences of 2 spreadsheets but the spreadsheets are so much simpler than mine. We have approximately 1000 employees.
I'm trying to write a macro that will create a pivot table, and am getting an Error code 1004: Cannot Open Pivot Table Source File "Sheetname". My code is below. I've tried to note what each section does, and it all seems to work well except for the Pivot Table creation.
I have a worksheet with two pivot tables, one of which is visible to the user. Ideally, the user should be able to change the "Row Label" field settings of the visible pivot table and then press an "update button" that then adds the same field to the second pivot table.
Ideally, the ordering of the fields should also be made similar between the two tables, though this is of less priority.
I imagine it would be something in the style of:
"If number of Pivot1 active row label fields = X then Pivot 2.AddRowLabelField = Pivot1.RowLabelField(X) end if"
attached is a spreadsheet 6 people in my area use daily(ive copied and pasted the sheet in question to a new worksheet, as the file was too big). Ive been trying for about 3 days now to make a pivot table to summarise this data.
I have a workbook with 12 sheets for monthly results and a Year to Date sheet. For presentation reasons, the monthly results are listed as seen in the first three worksheets, but I want the YTD results to be structured as seen in the fourth worksheet. I have shown how I am currently achieving this in the "Call" section of the YTD sheet, but as there are 12 months and many more variables in the original workbook, I'm wondering if there is any easier way than the one I'm using.
I have data that develops 3 to 4 pivot table each day. I would like to know if there is a way to change the date on one of the pivot table and have the other pivot tables date change to match with the first pivot table. At this time I am going to all 3 or 4 pivot table to select the correct date. The date is in the page position of the pivot table. I have attached a small sample of the data and the pivot tables.
I have a table of data which represents data in different categories by week.
My ultimate goal is to have another table representing the data for each month - for instance - for each of the categories the data for :
06/04/2009 13/04/2009 20/04/2009 27/04/2009
is summed to make the data for the month of April.
The way I'm doing this at the moment is very long-winded
I'm using a whole new table - the size of the original weekly one - for each month. A calculation decides whether to effectively leave a cell blank or insert the appropriate data based on a date being within a particular range.
So in each "month table" there is the same list of week values:
06/04/2009 13/04/2009 20/04/2009 27/04/2009 04/05/2009 11/05/2009 etc.
but for each "month table" only the cells adjacent to the dates within the relevant month will return actual numerical values within them
This is an example of the forumula I am using in these tables: =IF(AND(($AD7>=MIN($AE$5,$AE$6)),$AD7<=MAX($AE$5,$AE$6)),$C7,"NA()")
Then a master table sums the totals for each month.
I want to be able to keep this table but get rid of the ones for every month as the sheet is getting unwieldy!
I have tried several times to attach the sheet for clarity but each time upon trying to "Submit New Thread " I am getting page not found errors - the sheet is only 133KB and I have tried zipping and sending also - I can't make it any smaller.
I am improving an old spreadsheet that contains a tab for inputting the following data, all using data validation:
date employee (who did the work) team (for whom work was done) department (for whom work was done) type (of work done) Project (which property worked on) new/revision (was this a new project or revision of old) time (spent working on this)
I have a sheet with a drop down in which you can sort by employee and the associated chart and graphs adjust the information accordingly.
I need to create a chart and graph in which I can track the trailing month as well as year to date. Ideally, I would like to sort by the following:
- This month (i.e., the 1st of the month to present) - previous month (etc., be able to select all previous months for which data was entered, i.e. Jan., Feb. March. etc.) - Year to date
I know there is a way to do this - I thought maybe it would be through some formula incorporating =today() and subtracting back to what you need using numeric dates?
I have a pivot table in the first sheet which includes the field "Date" as a column label.
In the remaining sheets, except for one, there are pivot tables based on the same underlying dataset which also include the field "Date" as a column label.
I would like to adjust the selection (i.e., exclude some dates) from the column label in the first sheet and see if it is possible to make the same adjustments automatically to the pivot tables in the remaining sheets as well.
note that the field "Date" is used as a Column label, i.e., it is not a Report filter.
My input data for Pivot table has a column named "Month". The month values are like April 07, April 08, Nov07 in random order for period between Jan 07 to Aug 08.
When I create a pivot Table, this column is sorted alphabetically (April 07 is followed by April 08) but I need it to be sorted in the ascending order with respect to month (April 07 is followed by May 07).
I further use this data to plot a Pivot Chart. There is another issue here. I want to use separate colors for each series. I do not know how to achieve above 2 things.
I have a pivot table that I created and now I want to use the same pivot cache to create another pivot table instance on a different worksheet. how can I do that? My first worksheet gets saved as "OO By buyer" and now I want to create a new worksheet and drop the next pivot there.
I’m currently pulling data into two columns labeled “Monthly” & “Non-Monthly” respectively. They indicate work orders with a frequency of “Monthly” or “Non-Monthly”
The Monthly data is obtained using the following formula:....
I'm not grasping the Pivot Table correctly. I've written code to create a sum of values based on a worksheet. Specifically:LocationIDDeptSum of Hours Worked. Location, ID and Dept are rows. This effectively provides the aggregate values that I need based on the row groupings.Here's where this is falling apart. I need to create a new worksheet based on these values. I assumed the three row values - Location, ID and dept - would be in a hierarchy. It's possible they are, I just can't figure out the object model.
When I loop through the PivotItems collection of the PivotFields("Location"), I get what I need. However, I'm unable to determine how to loop through the child values (just for that location). PivotFields("ID") returns all IDs. I can't figure out how to return only the child entries for each pivot item. GetPivotData hasn't been very useful for this. As far as I can tell, GetPivotData, while its return type is listed as Range, throws an error when more than one cell is returned. Worst case, I suppose I can just parse the data in the DataBodyRange of the pivot table - maybe not, I haven't tried that. I'm hoping there's a way to iterate through these collections, but based on what I've seen from Google searches, there may not be. Does my pivot table need to be rearranged? I suppose I could also just dump this data into a data table
My macro is designed to look at a summary source tab and create a new tab for each unique project number. It then creates a pivot table from five different source detail tabs and filters on the project number. If a tab already exists it selects the tab and moves on to the next project number. There are six pivot tables created for every project.
New data is added each month to the source tabs and I have a macro to delete all pivot tables and the macro will recreate the pivot tables when ran again.
Issue: Running out of resources At work I'm limited to the use of Excel 2010 (32bit) so I'm restricted on 2GB of memory. At home I ran the file successfully (64bit) and it was around 3GB of memory.
My macro creates a new pivot cache for every pivot table where as I'm trying to only use 6 pivot caches in my coding. I kill it half way through and it's around 100+ caches causing unnecessary usage of memory.
Fix / Solution:
Correctly code the vba to only create six caches and code the rest the pivot tables to use that cache.The only difference in the Pivot Tables is that it’s sorted on the Project Number.
Code:
Dim VBAPPPC As PivotCache Dim VBAAPPC As PivotCache Dim VBAPRPC As PivotCache Dim VBAEXPC As PivotCache Dim VBAMJPC As PivotCache Dim VBAIVPC As PivotCache Dim VBAPT As PivotTable
1. I am trying to record a macro where I select a Pivot Table. But in the recorded macro "Pivot Table name" is not recorded neither the Pivot Field Property only the Range name is recorded. But on other systems (workstations) these details get recorded. Does this have something to do with excel settings?
2. I uploaded a macro enabled excel file on my company's "sharepoint" the drop down boxes present in the file get populated via a macro in "ThisWorkbook" page but sometimes these drop down boxes don't show any values. What can be the reason for this? Can't share the file because of data security policy of my organization.
I currently have several pivot table that's linked to a single pivot table(let's call it X) in the same workbook. I'm doing this to limit the file size because the data in X comes from a text file that has millions of lines. However, it's such a pain every time I need to update the tables because simply clicking "refresh" does not update those tables that are linked to X with new data. I would have to instruct the wizard in every linked table to point to X every time. I'm trying to write a small program to re-point to X for each of those other pivot tables whenever i refresh data. However, after trying to record the steps to do this I'm still unable to run these
