Pivot Table Update
I've created a pivot table that summarizes sales and gross profit on a quarterly basis and YTD basis by Market and store. I have a drop down in my source data file which changes the month. I go to update the pivot table and it no longer recognizes the data fields because the headings change to the subsequent month. Is there a way to not have to recreate the pivot table every time a run a new period? See below. Once I run August and then refresh the data it will not recognize these fields any longer.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Pivot Table Update Within Groups
I have a data table that looks somewhat like this a number of rows of "Date", "ID#" and other extraneous fields. I have a pivot table that simply lists the total count by date, then i grouped it by month and then by quarter: Date4 Date2 Date Count of ID 2008-Q4 76 October 2008 29 10/1/08 8 10/15/08 19 10/21/08 1 10/31/08 1 November 2008 25 11/1/08 2 11/15/08 19 11/21/08 1 11/29/08 2 11/30/08 1 December 2008 22 12/1/08 1 12/15/08 19 12/21/08 1 12/31/08 1 My problem is, when i add a new row to the main table, say with a date of "December 20, 2008", and refresh the pivot table, the information ends up clear at the bottom (in this case, after the year 2014). I figured the pivot refresh would place the data among the other December 2008 rows in the pivot table.
View Replies!
View Related
Pivot Table Formatte Change On Update
When I update the pivot table with new data, the column where I have some percentage numbers the formatting changes. One number goes back to standard. The new numbers that enter the column also enters as standard numbers. How do I get the column to stay in percentage numbers?
View Replies!
View Related
Pivot Table Update From Combo Box
I have a pivot table on a hidden sheet and a combo box the user picks from. I would like a macro or formula that could change my pivot table Page Value to that which is chosen from the combo box. Then update the pivot table. I can then use my report to look up values from this pivot as needed.
View Replies!
View Related
Update The Source Data For The Pivot Table In Database
I would like not to have to update the source data for the pivot table in my database. I've read that you can solve this by using a Dynamic Named Range and using that as the source for the pivot table. That way the pivot table will expand as new data is added to the database. The formula I used to create the DNR is:
View Replies!
View Related
Update Macro For Pivot Table From External Source
I have a pivot table created from a cube of data not within the file I am working in. I have created a macro that will update, but only for the specified timeframe. I have a separate worksheet within my file that will allow a manger to specify what timeframe he wants to see. Can I edit a macro to read from a source that is different than where the data is being pulled from?
View Replies!
View Related
Pivot Table Conditional Formatting Lost On Update
I am running office 2007. I am using conditional formatting in a pivot table to highlight rows where the value in the last column meets a particular criterion. However everytime I update the table the formatting gets lost. I have looked through all the threads I can find to establish if there is some VBA code that I can use to reapply the required conditional formatting after each update
View Replies!
View Related
Change The Date On One Of The Pivot Table And Pivot Table Match
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 Replies!
View Related
Months To Be Sorted In Ascending Order In Pivot Table, Want To Use Multiple Colors In Pivot Charts
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 Replies!
View Related
Refresh Pivot Tables Linked To Pivot Table
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 Replies!
View Related
How To Mimic A Pivot Table Without A Pivot Table
I have a list of items and their associated quantities, many items appearing multiple times. I need a concise list that summarizes each item and sums all of its quantities. The obvious solution is a pivot table. However, I update this list frequently and for some reason the pivot table is difficult to update. is there a function or simple vba code that I could put into this workbook that would work better than my unflexible pivot table?
View Replies!
View Related
Count Pivot Fields In Pivot Table
I am trying to find a way to count the total number of pivot fields in a pivot table so I can remove ghost pivot items that are no longer in the pivot table data. My code for this subroutine is as follows; Sub RemoveGhostPivotItems() Dim ghost As PivotItem Dim pt As PivotTable Set pt = ActiveSheet.PivotTables(1) pt.ManualUpdate = True For Count = 1 To 10 On Error Resume Next For Each ghost In pt.PivotFields(Count).PivotItems ghost.Delete Next ghost Next Count pt.ManualUpdate = False End Sub My code makes an assumption that I have 10 Pivot Fields or less. It would be nice to actually know the number of Pivot Fields so my "For Count" Loop would be more efficient. In otherwords;..............
View Replies!
View Related
Import Data From Access Table To Pivot Table - Enable Auto Refresh
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 ?
View Replies!
View Related
Apply A Filter In A Pivot Table And Extract Results In A Table
I have made a pivot table and I dlike to identify with a macro the documents with net value over 1000. Then extract these values next to the respective sales documents in an are near the pivot table somewhere. The fields are called Document and Sum of Net value. Of course the pivot is very variable one time it has 3000 records and another 5000.
View Replies!
View Related
Pivot Table An Extract Of Each Data Contained In This Table
i have a pivot table an extract of each data contained in this table. [img]Count of NAMdate SERVICENAM12-oct10-déc11-décGrand Total Commercial-lauralaura11 Commercial-laura Totalgh11 custody-jonathanjonathan112 k11 custody-jonathan Totalgh1113 settlement-ludovicludovic11 settlement-ludovic Totalgh11 SPQC-elodieelodie112 SPQC-elodie Totalgh112 Grand Total1337
View Replies!
View Related
Pivot Table: Table That Shows As A Header The URL
I want for my set of data. The attached .xls is pretty straight forward: the first column is a list of people (identified by their customer number) and the second column is the URL they visited. Since many people went to multiple pages, there are dupes between the two columns, but all of the rows are unique. What i am looking for is a table that shows as a header the URL (just one) and then the list of people that went to that URL under the header. So it's really just one column of information. It seems like a perfect task for a pivot table.
View Replies!
View Related
Pivot Tables: Auto-update 1 When Other Is Changed
I have 3 pivot tables on the same page - they all have the same PAGE and ROW fields, just different column and data fields. (may seem weird, but I'm producing reports for non-Excel people and this seems the only way to display info in user-friendly way). Problem: If a user changes the selection in a Page field, I need the other 2 pivot tables to automatically select the same Page field, so the pivot tables are still all like-for-like. I've started writing a macro which I can run to do this - below - but 1) I need it to run automatically when any of the page or row fields are updated 2) my macro is probably far too basic for my needs - one of the page fields has 245 values, so the below isn't really going to cut it!...
View Replies!
View Related
Update Master Table
I'm new to Excel Macros and have hit a brick wall on what I would consider to be my primary workhorse macro! I'm embarrassed to admit that I haven't mastered coding for movement of data around the spreadsheet much beyond the macro recorder. I need something tight, efficient, and (instructively commented)! I've got a spreadsheet consisting of a Master table with 42 rows, (2 rows per record), and 14 columns of information. To the left of that is another table consisting of live data from the process. It has anywhere from 1 to 24 rows with 10 columns representing Current/Live/Pre-processed information, pulled in from the company web location. Both tables start with an indexing column of 2 character alphanumeric IDs. They are as follows: C2, D3, D4, E3, E4, F3, F4, G3, G4, H3, H4, I2, I3, I4, J2, J3, J4, K1, K2, K3, K4. MASTER TABLE Index Column is B4 to B44 (2 rows per record, in A->Z order) UPDATE TABLE Index Column is Y4 to Y24 (Could be from 1 to 21 rows (Max), 1 per record, normally in A-Z order. What I need to do is Key on the Master index column for a particular ID, against the UPDATE index column. When there's a match I need to take the updated values from the UPDATE table and assign them to the appropriate cells locations in the MASTER table. Example: Starting with ID "C2" in the MASTER table, check to see if "C2" exists in the UPDATE Table Index. If there's a match the following cell value transfers must occur. Z4 -> E4, AB4 -> J4, AC4 -> J5, AD4 -> H4, AE4 -> K4 and AG4 -> I4. If "C2 is not found in the UPDATE Table then skip to next ID, (D3). If ID D3 is found, another cell value transfer must occur. Z5 -> E6, AB5 -> J6, AC5 -> J7, AD5 -> H6, AE5 -> K6, and AG5 -> I6. If "D3 has no updates then skip to next ID and so on. Continue checking IDs and updating until all IDs have been checked. I'm having difficulty crafting a macro to search 2 columns against one nother, specify the correct ActiveCell, than offset appropriately to place the desired value in the correct location.
View Replies!
View Related
Update Table Of Data
Cell A10 is set up to autocomplete based on the cells above it. Cells B10 and C10 are set up to use VLOOKUP to gather information from the table above based on the name in A10. What I would like is for C10 to autopopulate with the information in the table which corresponds to the name entered in A10, which it already does. What I would like to do after that is be able to change the data displayed in C10, and have it update in the cell in column C which corresponds to the name entered in A10. What I'm trying to do is create a customer manager. The customer's names will be hidden, as well as all their information, but I want my sales guys to be able to pull up an individual customer's information, then enter notes on that customer (column C). Vlookup won't allow you to enter information, as changing information erases the vlookup formula in the cell. This is very difficult to explain without direct visuals. Is there any way to do this?
View Replies!
View Related
Pivot Table With Dynamic, Updatable Chart, But Not A Pivot Chart!
My boss wants me to design a dynamic, updatable chart in Excel 2003. I initially made a Pivot Chart based on a Pivot Table which worked perfectly, but it doesn't look professional enough when printed (or viewed) and she wants me to approach it a different way. So, I created a graph based on the data in a Pivot Table, and used dynamic ranges as the source for the graph series so that the chart updates when the criteria fields are changed for the Pivot Table. I then added two combo boxes (ie data validation lists) to the Chart sheet, and wrote VBA code so that whenever the combo box values are changed, the Criteria fields for the Pivot Table on the 2nd sheet are updated accordingly, and this in turn causes the graph to be updated as well. This solution also worked perfectly, but now I've been told to create the graph without macros. Does anyone have any suggestions? The requirements/details are as follows: 1. The Pivot Table is on sheet "PIVOT", and the graph is on sheet "GRAPH" 2. The Pivot Table has two criteria - School Name and Year Level 3. On sheet "GRAPH" there are two data-validated fields, School and Year, which only allow the selection of valid Schools and Year Levels Is there any way to make the Pivot Table update when values are changed in the fields on the CHART sheet so that the chart also updates, but without using code nor a Pivot Chart?
View Replies!
View Related
Sort Table After Data Update..
I have a spreadsheet with 3 tables (all side-by-side). Table one has 8 rows of data; table two has 2 rows of data; table three has 3 rows of data. I am looking for assistance on how to sort each table in desceding order (by sales). Basically I want to do a sort everytime there is a change of values in any of the tables. I have attached a sample spreadsheet to clarify the table layout and the manner in which the values change within each table.
View Replies!
View Related
Update Table Based On A Master
I have one "master" spreadsheet I utilize per month. Each month my "master" spreadsheet is updated twice a week (so, for a four week month I update my "master" spreadsheet a total of eight times). To update my "master" spreadsheet I compare it to an "update" spreadsheet that contains the same column headings as my "master" spreadsheet. What I need updated: I need to compare account numbers (column A) between the two spreadsheets, once I find a match I need to update each of the costs for the 5 products (columns B thru F) from the "update" spreadsheet to the "master" spreadsheet. I currently have a simple SUM(IF) statement that completes this for the first update. I am lost at how I can have my "master" continually update for the next seven updates without losing each of the previous updates.Any help on this would be greatly appreciated. I am attaching two sample spreadsheets - "master" and "update".
View Replies!
View Related
Table To Update When A Category Is Changed
I am wanting this work book to do is have a table of categories update automatically when a category is selected in the "category column". The 3rd cell to the left of the category column "Gross column" is where the data is which needs to be sent to the corresponding category in the table and utomatically update. I have attached a sample workbook.
View Replies!
View Related
Auto Sort Table Upon Update Of Another Cells
I'm using a total of 20 Rows and 2 Columns. Each row has Column A for Description and Column B for Score. There are total 10 subjects with 10 noneditable rows, and 10 editable rows. I'd like to protect column A and B for rows 1 to 10 and unprotect only Column B for rows 11 to 20. When any cells from Column B, rows 11 to 20 updates, I'd like to automatically sort columns A+B for ONLY rows 1 to 10. Therefore, rows 1 to 10 need to be sorted based on the scores on Column B, and since rows 11 to 20 are just input fields, they are never to be sorted. Column B for Rows 1-10 will be formulas and are based on Column B values from rows 11-20. When a value in any of the rows 11-20 of Column B is updated, Range A1:B10 will automatically sort from highest score to lowest score (Column B). Since Column B for rows 1 to 10 contains formulas and not actual values, would it throw off the order of things when sorting formulas? the macro coding to perform this task. Here is just a visual of what the spreadsheet will look like ....A B ======= 1||A (B11) 2||B (B12) 3||C (B13) 4||D (B14) 5||E (B15) 6||F (B16) 7||G (B17) 8||H (B18) 9||I (B19) 10||J (B20) 11||A 10 12||B 9 13||C 8 14||D 7 15||E 6 16||F 5 17||G 4 18||H 3 19||I 2 20||J 1 The Bold are editable fields. and I want to use auto sort A1:B10 based on updated values of B11 to B20. I have also attached the basic excel file that situates my concern.
View Replies!
View Related
Update Source Data So Table Automatically Updates
I have two worksheets in my workbook; 1. Table 2. Raw / Source data. I have entered all the formulas into my table (sheet 1) referenced to the source data but I want to upadate the source data regularly but when I delete sheet 2 all the formulas come up with #Ref error. how i can update my source data so my table automatically updates
View Replies!
View Related
Dynamic Table: Get The Formula In The Cell To Update Dynamically
I have a table which takes the average of the last three years. The formulas are in cells below the data. The data is set up to be first data down to the oldest data. each time data is added, a row is put in on top. how do i get the formula in the cell to update dynamically. What would the average formula look like in the cell? Do you just say =average(name of file!RANGENAME).
View Replies!
View Related
VBA To Update Cells With Data From Microsoft Access Table
I have a spreadsheet and an Access database that contain the same items and prices. The database and spreadsheet are on a server shared drive. I would like the spreadsheet to automatically update its list of items and prices from the Access database whenever the spreadsheet is opened. Right now, we update the item and price list on both the spreadsheet and the database but I want to just update the database and have the spreadsheet pull the updates from the database.
View Replies!
View Related
% In A Pivot Table
I am trying to add a column to a pivot table that shows the percentage of another column. I have Sales and Returns listed for my company's products in the pivot table columns. Lets say the numbers are: Sales are 10 and returns are 4. I show both columns and want to add a 3rd column that shows 40% (4/10). Does anyone know how to do this in the pivot table? There are plenty of percentage options, but none to do this simple one.
View Replies!
View Related
Value 0 In A Pivot Table
This dataset I have has empty cells, but there is a formula in these cells. When I make a PT from that dataset, the cells which are empty in the dataset do show up in the PT, as zero's. Say in the first column of the table are employees, in the second column is a number that is of interest. I want the employees with a number of interest that has value of zero not to show up. Is there a way to leave those rows out of the table?
View Replies!
View Related
Pivot Table - Subtotals
I have a seemingly very simple question but even though I've worked a lot with pivots I can't find the answer. clientcode Amount countries a1 1.000,00 kenia a2 2.000,00 kenia b3 1.000,00 kenia b4 3.000,00 kenia b5 2.000,00 kenia c5 1.000,00 senegal c6 3.500,00 senegal c7 4.000,00 senegal c8 5.000,00 senegal Lets say I have a list like this and I want to count the number of clients (3) or countries(2). I can only get the total of rows per client but not the subtotal 3 for the number of clients. a - 2 b 3 c - 4
View Replies!
View Related
Pivot Table - Percentages
I am trying despeartely to finish this out. Here is the deal. I created a pivot table (see attached). The issue is that I need the numbers in the red boxes to be a percentage of the total number below - so the 2 should be a percentage of the 9 (22%) and the 3 should be 100% and the 7 should be 78%. I cannot seem to get this to work. Also, there are multiple rescue groups that need this and each needs to be the % of its own total number of animals.
View Replies!
View Related
Pivot Table :: How To Get The % Data Above A Particular Value
I have a excel sheet with following 4 columns Division Name: Location Name: Transformer capacity: Transformer earth resistance: Now how can I get answer of following queries in Pivot table. (Excel 2007) 1. % of a particular capacity of transformers in a division say % of 400 capacity transformers in all divisions. 2. % number of transformers having Transformer earth resistance value above a particular value in all divisions. Say % of Transformers having Transformer earth resistance above 2.6 in each division.
View Replies!
View Related
Sorting In Pivot Table
I have created a pivot table that contains the following data: Country Name Date as in 5/1/08, 6/1/08, etc With counts in the cells I can sort on Country name and get an alphabethical list by country name showing teh numbers for each date However I want to sort by the largest to smallest number that shows in the date column 01/01/08 Is there a way to sort on that colum-while moveing all the numbers in the other date columns along with it?
View Replies!
View Related
Count In Pivot Table
I have created a pivot table from a spreadsheet that had around 27 rows for each employee (i.e: each paycheck the employee received). The pivot table turned out great, but I need to know how to make it count how many employees are in each department and show it in the table.
View Replies!
View Related
Alternative To Pivot Table
I have a worksheet that has 5 columns of data, all of which are text. I am looking for a way to present/display the data in a manner similar to that of a pivot table. I'm pretty sure an actual pivot table is no good to me since I'm dealing with text, but I'm looking for something that is functionally the same. In other words, I would love to be able to "pivot" my data and display the different relationships between the different columns. If a pivot table would display text in the "data items" field, that would be perfect..
View Replies!
View Related
Pivot Table- Sumif
I was wondering if any one could guide me how to use SumIf or any other functions while creating a pivot table from excel data in a very presentable manner. The data shows - sales in qty to various customers for the month of may. Important columns need to be reported are Item, Qty. On the second tab (in the attached sheet) i am showing the format how i want. I need to sumif by item code and put the total in the respective company column. I could manually do this by SumIf but if I want to do this for month by month for 4 months then how would i do it quickly. Is there an easy way report for 4 month's comparative figures to make an analysis to see whihc company orders whihc product and the so on.
View Replies!
View Related
Updating PIVOT Table
I have created a dashboard based on a number of pivot tables. (Using GETPIVOTDATA tags) One of the fields in the Dash tab is Week which is a drop down menu of 1-13. (GLA Dash C3) I have two pivot tables in seperate tabs. (BottomQuartile1 and BottomQuartile2) both have Week as Report Filter. At the moment when I change the week in Dash from 4 to 5 i have to manually go into the two BottomQuartile tabs and alter the week to match. Is there anyway I can automate this process so that when i update the week in Dash it will update the Week in the other two tabs?
View Replies!
View Related
Dynamic Pivot Table ....
I have a spreadsheet the includes data validation, auto fill fields using formulas and conditional formatting all over the place. The user will be adding rows of data whenever applicable, so I want to create a dynamic pivot table that will update automatically. I think I've done it properly, Insert - Name - Define. In the Refers To field, I've added:- =OFFSET(Sheet1!$A$10,0,0,COUNTA(Sheet1!$A:$A),12) I've then based the Pivot Table on the defined name. For some reason, when I add a new row of data, the pivot table doesn't update, I have to do it manually. When I go back into Sheet1 and click in the Refers To field, there is an extra "blank" row showing as included in the data range (screen print attached) Is my OFFSET wrong, or does it have anything to do with the fact each row (down to line 300) contains formulas and #N/A's?
View Replies!
View Related
More Than One Column In Pivot Table
my table has the following fields: Zone (north, East, west, south), depots in each zone (D101, D102 in North and D201, D202 in South),product code (S101, S102...S100) then sales data for three stores 1, 2, 3. My original table has the depot code, zone it belongs to and sales of each of the product made to each of the store per depot. In the Pivot table, I shall need to show Branch and the Product codes on the row-side, and require store codes 1,2,3 to appear on the columns. The data area thus needs to be a summation of D101, D102 for North (for each of the product-codes) and D201, D202 for the South region. MY PROBLEM: I am unable to display 1,2,3 as separate columns.
View Replies!
View Related
Pivot Table Error
Upon opening an existing file, I receive the following error: Unable to Read File….I then click OK, Enable the macros and upon doing so, the following message appears: Excel Table Report on [Sales] Sheet1 was discarded due to integrity problems. I have two Pivot tables on the report – Pivot8 and Pivot1 – two separate worksheets but using the same source data. I have placed a button on each worksheet to enable the user to refresh the Pivot Table. Pivot1 is the table that is being discarded and when I click the button on Sheet1, the following Run time error 1004 appears: Unable to get the Pivot Tables property of the Worksheet class….when I select Debug, here’s the code with the bolded section being the point of error: ActiveSheet.Unprotect Password:="test" Range("K9").Select ActiveCell.FormulaR1C1 = "=NOW()" Range("K9").Select Selection.Copy Range("K9").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False
View Replies!
View Related
|
TRACKER
