Months To Be Sorted In Ascending Order In Pivot Table, Want To Use Multiple Colors In Pivot Charts
Sep 18, 2008
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.
View 9 Replies
ADVERTISEMENT
Oct 1, 2008
I need to build a macro that creates a pivot table that displays data for the last 13 months, by month (e.g. Sep 07 - Sep 08). My table has a column for months and another one for years. My question is: to run the macro succesfully, how should I build my table, or make the pivot table, in order to get the months displayed in proper order (Aug 07, Sep 07, Oct 07, Nov 07, Dec 07, Jan 08, Feb 08,.......Jul 08, Aug 08)
View 2 Replies
View Related
Apr 30, 2013
I have a pivot table and and struggling to group these by month as well as to sort thee in escending order.
Pivot Table ABC3Row LabelsSum of DebitSum of Credit
413/02/201334367.1822844.19513/03/201326475.492219.66613/08/201230307.613541.2713/09/2012
18898.0318065.4813/10/2012 7210.52913/11/201241969.041767.821013/12/201232844.7724041.26
View 3 Replies
View Related
Aug 16, 2013
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.
View 3 Replies
View Related
Sep 5, 2006
Is it possible to create pivot table from another multiple pivot table.
Example: I have two diff pivot table "Income" and "Expense" as well
and I need to preapare new pivot table using with those two pivot table
View 3 Replies
View Related
Jan 5, 2012
I'm trying to create a pivot table to figure out categories of expenses over the last year. The problem is when i create this pivot table on the horizontal i see all the days of the year, as opposed to categorized by month. I've tried to 'group' the months together but not very successfully. HOW to do this grouping elegantly?
I've attached 2 screenshots.
Pic 1 - Original State of the table with all the days on the horizontal
Pic 2 - an attempt to group by month, gone wrong
View 1 Replies
View Related
Jul 14, 2014
I use a spreadsheet to extract data from an access database. When the data is extracted it has about 10k line items and one of the columns displays the date. I formatted the cell to simply display the month (Jan, Feb, Mar, etc). When I create a PivotTable with the 10K line items each line displays the month. When I use the date as a filter it displays every single lines month in the filter box. Is there any way I can combine the months in the filter box so that each month would only display once collectively instead of for each line item?
PT.jpg
View 2 Replies
View Related
Dec 24, 2013
I have a table with two columns: 'Serial' and 'Read Date'. The table represents all those serials that failed to read at a certain time and date. For example:
Serial Read Date
4028543 10am Dec 16
4028544 10am Dec 16
4028566 10am Dec 16
[Code].....
I know you can move them manually but I have 73 reads so far and that's a lot of manually moving columns about.
I'd like to get Excel to use the original order of the Read date entries.
View 2 Replies
View Related
Mar 17, 2007
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.
View 4 Replies
View Related
Mar 17, 2014
I have two separate data tables (with separate named ranges) and two separate pivot tables (the pivots each refer to the separate tables) all on the same sheet. For each of the pivot tables, I would like to have a separate pivot chart. In the pivot charts, when I click "Select Data", I find that the "Chart Data Range" for the pivot chart is grey (uneditable). Is there some setting that I need to toggle to enable the data source range so that I can change it? Also, when I try adding a new pivot chart to the sheet, this option is also grey/unavailable.
View 1 Replies
View Related
Jul 4, 2012
Excel 2010. I set up a trial balance as following:
1/31/2000 2/28/2000 3/31/2000 ------- 12/31/2012
Acct # 1
Acct # 2
Acct # 3
Acct # 4
I was able to use pivot table to set up the balance sheet and income statement.
1. now each month is a field which I can choose to add to the report, but the field list is getting too long (and growing). Is there a way I can insert 1 field (for the user to input the exact date) or 2 fields (to input year and month) and the pivot table can pick up the input and refresh either automatically or manually.
2. I looked into the "group" function in pivot table, but how do I group the above data in quarterly and yearly fashion so the user can get those information quickly?
View 1 Replies
View Related
Jul 23, 2012
I have entered a custom sort order under 'first key sort order,' but the field is not sorting according to the list.
I'm sure I entered the list correctly, because I actually entered it a long time ago, and it was working for a while. However, today the underlying data was temporarily erased, and the pivot table, when refreshed, basically went blank. I restored the data and refreshed the table, but this field is back to sorting in the wrong order. Refreshing the pivot table and re-choosing the appropriate sort options do not work.
View 1 Replies
View Related
Dec 20, 2007
I have a spreadsheet with multiple pivot tables and graphs. Is there a way to add a scrollbar on one sheet so that when you scroll through it, it will display(one by one) each graph that I created with the pivot table(s) so that they're all on one page(still dynamic) as opposed to them being on multiple worksheets
View 2 Replies
View Related
Apr 28, 2014
Cells in row C have numbers (number of days between date a and date b)
I want to group the days so I can run a usable pivot table as follows:
Up to 7 days between update date and today (ie 1 week) 8-14 days between update date and today (ie 2 weeks) 15-21 days (ie 3 weeks) 4 weeks +
My attempt is as follows but only give two results and not 4?
=IF(OR(C2<=7),"1-7days",IF(OR(C2>=8,C2<=14),"8-14days",IF(OR(C2<=21,C2>=15),"15-21days","Over4weeks")))
View 3 Replies
View Related
Jan 29, 2010
I need now is to provide a separate filtered list to the side (Starting from column M.
In the quickest way possible (as the data is going to stretch to 2-3 thousand rows I suspect :s), I need to, after selecting a track of interest using a list box on the sheet (Eg Brands Hatch), and a season (eg 2006, 2007, 2008 etc or ALL), and a race class (Eg A, B, C or ALL), have a macro display the data matching the selected criteria to the left of my data from column M1. This needs to be ordered with the smallest lap times (from column F) at the top...
The idea is that I can select a track, and some filter data and display a history of the fastest recorded laps at that circuit from the long list of data I have. I can't use the filter function within Excel as I'm going to need to generate and copy this data for export to an output file later on.
View 9 Replies
View Related
Mar 19, 2013
On a worksheet, I created:
- 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.
View 3 Replies
View Related
Jan 4, 2010
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.
View 14 Replies
View Related
Mar 14, 2013
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"
View 1 Replies
View Related
Jan 22, 2007
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.
View 6 Replies
View Related
Apr 30, 2014
I have some excel sheets that are formatted like the following:
COMPANY | TOTAL | R | G | B
company1 | 10 | 255 | 000 | 000
company2 | 20 | 000 | 255 | 000
company3 | 30 | 000 | 000 | 255
...
and so on...
My question is that I would like to have a macro that runs on this basic file and creates a bar graph with the data. Then it utilizes the RGB values in the columns to change the specific bar for that row. So setting the r, g, b as variables corresponding to the columns in the sheet. Also there isn't a preset number of rows in the files.
[URL]
View 13 Replies
View Related
Nov 12, 2009
I have date fields as column labels in a pivot table. When I try to group them I'm only given the option to group by integers and not by months, years etc.
I've had a look at the format settings of the column and they are formatted as date fields.
View 9 Replies
View Related
Apr 29, 2006
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.
View 2 Replies
View Related
Oct 9, 2008
I have a problem with the formula that look up multiple records with the same values and return multiple corresponding values in ascending order. I am using Excel 2003 and it is a bit complicated to explain so I have attached a sample spreadsheet to show what I mean.
What I want was after I have sorted the occurrence value in column E based on column B and I want to correspond the Rank in column D based on column A in ascending order for the same occurrence value in column E.
Eg: There is two occurrences for number 1 at E3 and E4, and three occurrences for number 2 at E5, E6 and E7 in column E. Then the Rank for the first occurrence for number 1 in D3 should be ranking 6 and the second occurrence for number 1 in D4 should be ranking 7, so the Rank for the first occurrence for number 2 in D5 should be ranking 3, D6 should be ranking 4 and D7 should be ranking 9 based on column A and B, etc.
View 3 Replies
View Related
Jul 7, 2014
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.
View 1 Replies
View Related
Jul 22, 2012
I am looking to create a sheet that automatically updates itself in alphabetical order, when we enter any data.
here is the case:
- I've 30 secondary sheets and 1 master sheet
-----------------------------------------------------sheets detail-----------------------------------------------
Master Sheet detail:
master sheet's column A contains name from A3 to A100 (user can edit this column)Column B to to AM contains data corresponding to each name in column A
Secondary sheets detail:
column A contains name from A3 to A100 which are linked to master's column A (means any change in Master sheets' column A will change this column).................. (user cannot edit this column)column B to I contains corresponding data.
------------------------------------------------------------------------------------------------------------------
I want that when user enters a new Name in master sheet and presses "Enter" then:-
Column A of master sheet should automatically arrange itself in ascending order, along with all the corresponding data in column B to AMsince secondary sheet's column A is also linked with master sheet, so they should also be updated along with their data in corresponding columns i.e B to I
I've tried other websites and solutions for sorting of rows but since i've little experience with excel VBA, things are not quite working for me.
View 5 Replies
View Related
Jan 7, 2014
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
View 1 Replies
View Related
Mar 13, 2014
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
[code]...
View 1 Replies
View Related
Apr 20, 2014
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.
View 1 Replies
View Related
Jul 25, 2006
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
Sub Macro1()
ActiveSheet.PivotTableWizard SourceType:=xlPivotTable, SourceData:= _
"PivotTable1"
End Sub
View 6 Replies
View Related
Dec 29, 2012
I am trying to sort multiple values in ascending order (example attached) however I can't get this to work. Tried looking at a few different forums and although there is plenty of sorting questions out there they all tend to be relating to dates not different values (i.e text and numbers).
View 14 Replies
View Related
