Is there a way to freeze your pivot table format after clicking for a ' Refresh'. I'm getting tired reformatting my column headers to wrap or in the middle etc. I'm working in Excel 2007.
View 6 Replieswhen I refresh data in a pivot table. Each time I click 'Refresh All' it alternates between a refreshed version (up to date with source) to an earlier set of data. This is concerning because operators will not be aware that they are not using the most up to date data 50% of the time.
View 3 Replies View Relatedvba in excel 2013 pivot table that updates from an online CRM rows are a to h and it can be any number of rows. What I am trying to do is track progress. In column f values indicate probability for success 10 25 45 90, which can go up or down. The pivot table is refreshed to get the latest values from CRM. the update is handled by a connection to the crm not in the vba.
So far I been researching methods to conditionally format values that went up, down or remained the same since the last refresh with up down and across arrows. I have managed to piece together what I think should work but alas it is not. So I have come to you internet.
My code first clears any formatting and goes down the column avoiding null or empty cells, comparing the values in column f to values in column j.
I have 3 conditions greater than, less than or equal to, and would like add an icon for each based on the result of the comparison.
Finally when it finishes the column the code copies the current values in the pivot table column f to column j outside the pivot table which i hope to be able to hide once the cf works. The code is below
[Code] .....
I am stuck trying to sort data that looks like the following, into a format that I can use as source data for pivot tables/charts.
Excel2007ABCDEFGHIJK2MalayMalayMalayMalayMalayMalay
ChineseChinese3MaleFemaleFemaleMaleFemaleFemaleMaleMale
436-4536-4536-4525-3525-3525-3536-4536-455
DateArticleContentLapseCurrentCurrentCurrentLapseLapseCurrentCurrent612-SepRelieving 40 years - Oct 15, 1972
Thick Haze continues to blanket MalaysiaHistory42712-SepRelieving 40 years - May 5, 1972
All Sharifah wants is a pair of legsHistory1222812-SepA man and his agonyHistory3223912-SepA lesson on Sept 16History22Sheet2
I will need to group Data multiple ways. However, I cant work out a good way to sort it. Can I have a suggestion on layout that I will be able to arrange data appropriately.
(Using Excel 2007)
What would be the quickest and most efficient way of doing the following:
Example
Sheet 1: clicking once, twice, three times in the following cells to change the fill to Red, Yellow, Green respectively (in Row 22, COLUMNS E H K N Q T W Z AD AG) and mirrorring this format/fill on:
Sheet 2: , mirror the selected fill on sheet1 in E22, H22 , K22 in in E3:N3 respectively example E22 (sheet1) is mirrored now in E3 (sheet 2); H22 (sheet 1) is mirrored in F3 (sheet2) etc etc.
I have 35 tabs formatted in the same way - each tab, as with Sheet 1 is linked to a different row on sheet 2 in the same range as is sheet 1 and would need to replicate the mirorring on Sheet 1 to each of the other 34 tabs.
I am using Excel 2007.
I have some pivot tables that have the data source in another workbook. I didn't create any external data connections. When I created the first pivot table, I just selected the range from that other workbook. Most of the other pivot tables were copied and modified accordingly, so they should have the same pivot cache.
I have created a macro that basically refreshes all data, using the Workbook.RefreshAll command, then copy/pastes values and then deletes the pivots so that I can send it via e-mail without problems. The macro seems to work fine.
The QUESTION relates to the fact that I am afraid VBA won't wait for the refresh to complete before starting to copy/paste values.
The PivotCache.BackgroundQuery = False solution doesn't work. I get 10004 error. Also, there are no data connections visible in the Data > Connections. I guess that, because of this, "Properties" is greyed out in the Data tab and also "Connection Properties" under Pivot Table Tools > Options > Change Source Data is greyed out. This way, I cannot uncheck the "Enable background refresh" box.
I could just tick the "Refresh when opening the file" in Pivot Table options, but if I open the file without first opening the workbook with the raw data, refresh takes very long.
I have tried to measure time difference between the RefreshAll command and a simple Range("C3").Select command. There is a time difference, but I'm still not 100% sure that VBA waits for refresh to complete before continuing.
VB:
Do Until Application.CalculationState = xlDone
VB:
[COLOR=#333333][COLOR=#333333]DoEvents[/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333]Loop[/COLOR][/COLOR][COLOR=#333333][COLOR=#333333]
[/COLOR][/COLOR]
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?
View 3 Replies View RelatedI have some code that runs and loops through each pivot table to refresh it; however, I remmed out the code so that it only loops once - telling it to refresh the first pivot; the thing I'm confused about is that after the code runs, it appears to have refreshed all my pivots. I think it's also important to note the each pivot table is linked to the same data source. (an external ds)
Maybe when this is the case, refresh will always refresh all?
If a user changes some data in a pivot table and then refreshes the table, the changed values are supposed to revert to the source data, right? This does not happen in my pivot table.
Any changes made in the underlying data get transferred to the pivot table refresh, as expected. But if I change some values in the pivot table, followed by a refresh, then the cells that I changed remain the same unless I happened to modify the corresponding values in the underlying source data too. In other words, the refresh operation does not touch the pivot table except specifically where an update is driven by a change in the underlying data.
This behavior is different from what i remember from, say, excel 97 / 2000.
Will someone please tell me the difference (if there is a difference) between the following 2 lines of ....
View 6 Replies View RelatedI prepared a Pivot Table and then i add some data in it but now all data is not showed by Pivot Table.
I do all necessary things like refresh and all but unable to increase a range.
How to increase range for Pivot Table in 2007.
I have a pivot table in Office 2007. I want to filter the last column such that the values in the data area are greater than a certain number. But all those filter options are grayed out. The only option available (and working) is to select the top n entries. So clearly a reference into the data field to filter a column works, but why not by value?
View 9 Replies View RelatedI cannot remember how/if I can ALWAYS exclude blank results from my pivot table.
I'm hoping i don't need to use a macro, it should be in the settings for the pivot table, I hope...
I use Excel2007.
In my data, I have:
1. Age (up to 20, 21-30, 31-40)
2. Gender (male, female)
3. Location (London, Paris, Berlin)
I want to see Age and Gender as column labels and Location as row labels. However, I don't want Gender as a sub-set of Age - I want Age Labels followed immediately by Gender labels ie:
Upto20 / 21-30 / 31-40 / Male / Female
I don't want: 21-30 Male / 21-30 Female / 21-30 Total / etc etc..
Is there a way to specify this in a pivot table?
In Excel 2003 I created a macro that copied data from a table, pasted into a new sheet, created a pivot table, copied and pasted that data next to pivot table, sorted, copied and pasted into chart data. I've used this macro in Excel 2007 with no problems.
Now I'm trying to create a similar macro but it won't complete the pivot table. My Pivot Table Field List should have the Row Label and Values populated but when I run it all that is populated is the Values field.
Is there something in 2007 that will not allow me to create a macro using a Pivot Table or am I doing something wrong? The code is below if that helps ...
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
I have a pivot table that summarizes jobs that need to be completed on a specified date. I need to put that information on a calendar, but I would like to concatenate all information from that date so I can do a vlookup from the calendar. My other problem is that the number of cells change for each date, so I cannot specify exact cells, (some only have 1 entry, but others may have 4 or more). Can I specify a range of cells based on the result of the pivot table?
I have attached the pivot table, calendar and the data sheet with the vlookup info. Am I using the proper calendar or method to find this info?I have struggled with this for over a week now.
I have created a pivot table using a family name in row labels. The names appear in alphabetical order unless the person's name is the same as a month or day of the week. The result is that I have Mr Sun and Mr May at the top of my list, rather than listed alphabetically. Sorting the list does not solve the problem. It switches Mr Sun and Mr May, but does not included them in the main list. Is there a setting I can use that will stop Excel 2007 thinking that these words are something that they are not?
View 4 Replies View RelatedI believe that I am finally getting the hang of pivot tables and VBA ... pretty nice. Now for my latest frustration - calculated fields.
I have a pivot table created which compares two years of data. The problem seems to be that this data is from the same data field (PINSAL) even though it shows in two columns (year 2007 and year 2008).
1 - I need to subtract the 2007 figure (column C) from the 2008 figure (column D) in a calculated field called DollarVariance
2 - I need to divide DollarVariance into the 2007 figure to create a calculated field called PercentVariance
This seems easy to do if I had two different variables used to create the 2007 and the 2008 data but it is the same datafield. Can I use column letter? Can I use the column name assigned by the pivot routine (12 - 2007 and 12 - 2008)?
I have a pivottable that has a calculated field returning a percentage of two other feilds. I have pivotcharted the result and now want to include a median of the calculated feild results on the same chart.
I want to use a pivot chart as it'll accomodate changes to the data range and different page fields.
using xl2007
I'm using Excel 2007. My pivot table seems to be limiting me to 256 columns in the Values/Data area. In researching below I believe that I should be able to have 16,000 columns in my Pivot Table.
[URL] The "Big Grid" and Increased Limits in Excel 2007
PivotTables Maximum rows displayed in a PivotTable report is 1 million.
Maximum columns displayed in a PivotTable report is 16,000.
Maximum number of unique items within a single Pivot field is 1 million.
Maximum number of fields visible in the Fields list is 16,000.
I have a table of data which I am analysing in a Pivot Table. For the majority of the data, the Pivot works very well: however I have a small issue, but it subsequently means the Pivot is useless.
Within the table array that I am referring to, there is a column of data of "Days per employee for a given period". The rows of data within the table array relate to every absence entry per employee, but this final column of data always contains the same figure (although can differ from employee to employee).
When I put the data into the Pivot, I can summarise the absence(s) as a simple sum. However, this final column of data should not be summed, since it is already the sum figure.
This figure though needs to be part of the Pivot, since I need to report on the percentage of absence days per type over the given period. So, the simple representation of =absence day(s)/worked days does not work... well I cannot get it to work. Additionally I have tried features like % of, but nothing.
I am using excel 2007.
I have a stripped down data source for debugging purposes. I only have 4 rows of data for test purposes. The dates are formatted as dates.
When I create the pivot table the dates become my column values. When I select the first date in the pivot table the Group By Field menu option is grayed out. I tried setting a tabular format but didn't work.
In Excel 2007 is it possible to filter the "Values" in a Pivot Table?
I am tracking the Gross weight of shipping containers. My table sums the weight of all items in a container by container number. So my rows are 9 digit container numbers and my data values is a Sum of part weights. I want to be able to filter out containers above a certain weight.
for example:
Row Labels Sum of Gross Wt. - Lbs
10003150588929
10003153258700
10003155984958
1000315651530
10003156549761
I know I can simply copy and paste into a new tab and sort it there, but I'd like to be able to do it internal to the pivot table if that is possible.
Using Excel 2007 I have a pivot table that counts that number of incidents based on month and year. To get the month and year I group the date field as months and years.
My problem is if I want to filter specfic months in say year 2012 it also takes out the month in 2013. I though it used to give you the option of year and date in the filter but mines are 2 seperate filters.
I have enable Refresh on Open for my excel pivot table, but user need to click "Enable Automatic Refresh" , only solution i came across is to change the registry setting. Which i dont have access to edit registry(admin disable the access).
Alternate solution i try to use Access macro to automate the process and use Outputto save it as a excel file A. Then use excel file B to update pivot table from excel file A.(as excel A data is always latest)
The problem is i will get "....A file name already exist...do you want to overwrite.." prompt.
Which defeat the automate process.
Any other solution to enable the automatic refresh on open the excel workbook?
Or Access can overwrite the exist file or save it as another file name with timestamp ?
I am using Excel 2007 and my version of visual basic is 6.5.
I am baffled by the behavior of this code to manipulate one of my pivot tables. I am trying to set all but one of the pivot items in one of the pivot fields to not visible. Because there is a large number of items, I wish to suspend all automatic updates until all items are properly set to visible or non visible.
------------------------------------------------------
Sub SwitchBoards()
Dim BoardNew As String
Sheets("Board Parameters").Select
' Make sure we get the right value.
[Code].....
The MsgBox returns with: "Manual update is set to False" right after the instruction to set it to True!!
I am trying to create multiple pivot tables from the same pivotcache using VBA. The data range is approximately 270,000 records with 100 columns. When I run the macro, I get "Run-time error '-2147352567 (80020009)' Method 'Add' of object 'PivotCaches' failed". Is there a size limit on the data? If there is less than 65,000 records, it works great; if I use a wizard, it works with no problems with the full dataset. If I bring the data in as an Access table, it can work with some tweaking of the code, but the size of the file is huge because each pivot is taking its own snapshot of the data. I also can only create 3 tables before it runs out of memory, so I have to stop, save the file, and open it back up to create 3 more pivots.
Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
[Code] .........
The last line is where I am getting the error. I am running XP, Excel 2007.
As I said, if I import an Access table, I can use the code:
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Table_Pivot_test.accdb[#All]", Version:=xlPivotTableVersion12). _
CreatePivotTable TableDestination:="", TableName:=txtPivotTableName, DefaultVersion:=xlPivotTableVersion12
And loop, but the final file size with 14 pivots can be 500MB! I have tried both "Create" and "Add", but they both give me errors.
I am trying to have duplicates and their corresponding data appear in my pivot table. Assume I have a list of over 1000 stores. Let's say store 101 appears twice in my data file.
When I create my pivot, the 101 only appears once and the data is consolidated. Is there a way to ungroup/display duplicates?
I am looking for a way to extract all of the unique items in a Pivot Table. For example, I have a Pivot Table that has category A items and category B items. There may be several B items to one A. For example:
A0
B1
B2
B3A1
B3
B4
B5
Now, what I need is a list that looks like this:
A0 - B1
A0 - B2
A0 - B3
A1 - B3
A1 - B4
A1 - B5
Formatted so that those are two columns. However, in Excel 2007, I cannot set the option to have it repeat the A series items in the Pivot table. Is there some easier way to do this?