I have created a Pivot Table to sumarize the monthly sales data.
Is it possible to select just one month and show the YTD sales?
e.g. We are in September, I select August the Pivot Table should show the August sales column and YTD (Jan to August) sales column.
See the attachment. I want the percentage of Car Sales to total sales of different countries automatically.
View 2 Replies View RelatedI 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.
For example, I would like to turn this:
3/2/2005 $xxxx
3/5/2005 $xxxx
3/20/2005 $xxxx
4/2/2005 $xxxx
4/10/2005 $xxxx
Into this:
March 2005- $xxxx (monthly average)
April 2005- $xxxx (monthly average)
I have a feeling some 'countif' formula would work but I am not sure how to do this.
Basically, I'm doing a recorded macro for work where I take an export and manipulate the data to show differences between sales from last year and this year. Also comparing this months projected sales to avg of last 6 months and also against last years this month.
The problem I'm running into is in automating the this month sales for mid-month exports. I can do it individually but I can't find a formula that will do it. Data is in one cell per month, so ex. 130 sales this month so far. I need to have it convert that to projected sales for total month based on what day it currently is.
I have table with companies across in columns, months down the side in rows, and 6 parts to the data in addition to company and month. I have quotes from 3 different sources, and won deals from those 3 different sources. How do I create a pivot table/chart to analyze the data quickly, or find out Top 10 companies quoting with value and top 10 companies with won deals?
I have attached an example, taking out the company names with A, B, C, and source with A, B, C. The cells do have an array formula which picks data from another spreadsheet. Which, by the way, is really slowing my machine down each time I type something into the spreadsheet.
Quotes.xlsx
I have a sales level that I need to track...My rolling 12 months' sales must be $85,000 and my currently monthly sales must be $7,000. I have a sheet that tracks the $85,000 and tells me what I need to achieve that, but I haven't figured out how to include the $7,000 monthly minimum....
The chart below is what I have. So for example, this month it's telling me I only need to sell another 3016.46 to hit the $85,000 rolling 12, but I actually need to hit $4821.79 to meet the $7k minimum.
Actual Rolling 12 Goal
Sep 2012 5,367.24 73,663.30
Oct 2012 5,649.93 69,496.28
Nov 2012 14,163.38 73,451.30 [code]....
'Pivot Table Calculated Fields" - Below is my pivot table.
I need the pivot table to subtract 'February' sales figures from 'January' sales figures to get the difference (variance).
I know the following steps:
1) Click on the Pivot Table
2) Choose "Formulas"
3) Choose "Calculated Field"
4) In the "Name" field - type in the name that I want "Two Month Variance" (for example)
5)......then I don't know what to do
Sum of Quantitymonthsupplier idjanfebGrand Total
754466163150842519337568263901785022875682639410025035075682639500
75682639642523866375682639780012492049756826398209209756826399292292
756826400264264756826401178178757494037292292761034919209209867233456264264(blank)
Grand Total245154236931
Need to create year to date sales comparing 4 years month by month. Stacked chart (Excel 2010) works OK for the first three months but adding the fourth month changes the chart to 4 series with a monthly axis. To put it another way I need a vertical axis of years and a horizontal axis of $$$ with each months sales of each year stacked on its year.
View 10 Replies View RelatedI need to display a value that is in a table on one sheet when the month is matched on the first sheet.
The month that I need to match up is on sheet1 in cell B1
I have a table on sheet2 that is a year budget, the cell range for the months are C5:Y5
Under each month I have a list of headings and it is these cells that I need to display on sheet1 in the relevant box.
I have a pivot table with slicers. I would like to collect into a cell all the item names that have been selected (filtered). (through VBA or Formula) For example if I select A, D, and E into the (A, B, C, D, E, F) list. I want the labels A, D, E to appear in a cell.
[URL]
I'd like to check whether all the filters in a pivot table is selected or not.
I am currently using the following code:-
Code:
Count = 0
For i = 1 To Worksheets("WW").PivotTables("PivotTable6").PivotFields("Country").PivotItems.Count
[Code]....
where flag is to flag whether all fields are selected in the pivot field or not.
The problem is the above code is very time consuming.
I have a pivot table with slicers. I would like to collect into a cell all the item names that have been selected (filtered). (through VBA or Formula) For example if I select A, D, and E into the (A, B, C, D, E, F) list. I want the labels A, D, E to appear in a cell.
[URL]
I am having difficulty with the pivot table I created in that the name of the month is showing the serial number. In my spreadsheet, the data I have is:
cell B3 = 20070501
cell P3 = 05 { formula =MID(B3,5,2) } {}*not an array formula
cell Q3 =May {formula =DATE(2000,P3,1) }
custom formatting in cell Q3 with MMMM to retreive name of month
In my pivot table, I would like the name of the month "May" to be displayed but 5/1/2000 is appearing. What am I doing wrong? Must I always format my data in the pivot table to return the name of the month? Perhaps someone in the Excel spectrum knows of a better solution.
Thanks to anyone who can guide me to sanity.
I am trying to form a macro in VBA that will basically uncheck the "Show All" function of the Pivot Table filter user interface, and then select only the one PivotItem that I want. In context, I have about 50+ different project numbers, each with a different worksheet and its corresponding pivot table. Here is what I have so far:
Dim pvtitem
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Project #")
For Each pvtitem In .PivotItems
pvtitem.Visible = False
Next
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Project #")
.PivotItems("525064").Visible = True
End With
When I try to run it, it gives me the error: "Unable to set the Visible property of the PivotItem class". It looks like the error occurs in the loop part of the macro.
I have a range of data over approx 300 rows. The first column contains dates.
I have created a pivot table and want to group the data by month. I have right clicked the table, grouped by months and selected the whole year as the date range.
For some strange reason the pivot table starts at 1/1/14 but ends at 21/2/14? It will not show the whole year.
I have a couple of Pivot Tables that I create each month summarising my Income and Expenditure. The only problem I have is making the columns appear in the correct order, i.e. where July is month1 through June(the following year) being month 12 to coincide with my financial year. Oddly enough last month (January) was fine, following December.
This month January and February appear in columns 1 & 2.
Is there a way to have a Pivot Table show only the Top 10 items based on dollar amount. Data covers a month of daily activity (+/- 250 rows), but i only want the Top 10 items based on Dollar amount. Is this possible?
I know filters can do top 10 but it doesn't consolidate similar items.
I have a list of Insurance payers - 20 or so. They are listed in a Pivot Table on sheet2 with rate data and such. On sheet1 I have a listbox with the Payers listed and can be multi selected by the user. I wish to have the user select some payers in the listbox, goto sheet2 and view the Pivot Table only containing those Payers selected from the listbox.
View 4 Replies View RelatedI have data that has a customer name and the month that they were taken care of" (of course not spaces
Customer Month
Smith January
Smith January
Mark January
Suzy January
Smith February
Mark February
I want a pivot table to say:
Month #customers
January 3
February 2
but when I do the count it adds the total number of customer names and gives (it is counting smith twice in January
Month #customers
January 4
February 2
I was reading something about doing the count (so it shows 4) and then doing an index within the count but I keep just getting 1 as the value (for both months).
I tried doing a formula in the document that said =IF(ISERROR(MATCH(A2,$A$1:A15,0)),1,0). basically looking to see if the name is above the location I am looking at and if it is to put a 0 if not put 1 and then do a sum of that column....trouble is that when a customer has orders on more than one month, the second month is coming up with a 0 instead of a 1 (and this happens quite often).
I am not sure what to do. if the customer shows up in more than one month I want them to count towards the unique count of both january and february...we look at each month separately.
I have written two macros to create a pivot table that displays data by month. I created a column that is a flag that indicates if the if an instance occurred in the last 4 months(later used as a filter). The issue is how to handle defining the last 4 months. If the current date is prior to the 16th, I want to define the last 4 months as not including the current month. If it is after the 15th, I want define the last 4 months as including the current month. Currently I have two different macros and I make the decision on which to run. I would like the macro to handle this for me. Below is the cell formula that I use to set the flag if it is in the first half of the month.
ActiveCell.FormulaR1C1 = "=IF(RC[-3]>EOMONTH(TODAY(),-5)+1,1,0)"
In my Pivot table I have 3 fields in the "header - section" of each row
There is also the possibilty in the Page section to choose between subjects (eg physics, chemistry, biology etc) When all rows are displayed there are 68 in total
When I choose Physics there are about 30 customers that have a value in 1 of the rows. Excel shows 30 rows, but I would like all 68 row to be shown, because these are the values that are important to me. I have tried a lot of settings in the pivot table but can't find the correct 1.
What happens a lot is the the rows are "multiplied", meaning that the 1st row header has every combination of the 2nd and 3rd and so one. Which setting is needed to get what I want?
In the attached file (xlsx) under 'Database' Tab poeple have indicated their preferences (multiple choices) for different food items "specialties". The specialties are grouped under broader buckets called "groups". The specialties are bucketed into groups in a way that people end up more than once in each specialties and groups due to their muliple selections. When we create a pivot by specialties (Pivot Specialties tab), each person appears only once for each specialty...it's great. But, when we create a "group" pivot (each group has multiple specialties), now, people appear more than once for each group. Is there a way, each person can show up only once under each group so the group count does not appear to be misleading?
View 3 Replies View RelatedI am building a Pivot table and I need to show ONLY the Grand totals at the end of the table. In the example below I want to show only Total volume Stage movement at the end and not the subtotals in the columns.
Column Labels
Pipeline
Best Case
Commit
Closed
[Code]...
I am trying to to create a macro that will complete a Pivot Table, then use the Show All pages function. The data has the same number of columns each time, but the rows will differ each and everytime. The content of the column which houses the data which eventually will be a page can differ as well. I continue to get an errro and below is highlighted when debug is selected.
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Table1", Version:=xlPivotTableVersion12).CreatePivotTable TableDestination _
:="Sheet4!R3C1", TableName:="PivotTable2", DefaultVersion:= _
xlPivotTableVersion12
This is a very repititive pivot that is done many times during a month. I have tried just using the data as is then the pivot, and converting the data into a table, doesn't matter still get the error.
I would like to be able to filter my pivot table to shows the rows with no data ?
I can make "no value" appear in the pivot table in 2 ways - either by using column field as well as row fields or by using the "show items with no data" option in value field settings but I cant get filter to show only the "no value" rows
Sum of rev
Column Labels
Row Labels
[Code].....
When you double click on a number in a pivot table data area you get a new sheet with that information showing the detail, is there anyway to "trap" this action?
I want to run a macro on the detail data but can't see a way to do it automatically, have tried, BeforeDoubleClick, PivotTableUpdate, NewSheet events but all have there draw backs if you are doing something other than showing the detail for the pivot table. Of course I can just run the macro after the sheet is made but it would be nice to do it on its own.
I want to make a calculation in a pivot table where a percent difference is calculated by year.
The % difference from calculation does not show an increase from the previous year as 100% but a DIV/0 error. Can i make a custom formula that will use the year base field.
I have recorded the below macro to select a pivot table field (on demand) and show the information on anthother sheet tab.
Obviously if the order of the fields change then the macro is buggered, any ideas so it only selects the on demand field and shows that info.
Sub Macro1() ....
Can I do something so my pivot table shows Manager name in all appropriate cells (eg. agomes is A3:A4 and bschaefe in cells B5:B13)? ........
View 9 Replies View RelatedDoes anyone know a setting to display pivot tables as a % of sub total automatically within the Pivot Table Settings? I have recreated a formula on the cells H:K of what I am looking for. see Attached.
View 2 Replies View Related