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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Pivot Table - Using A Dynamic Range
Because my pivot table is used by several users the data source changes often. In response, I created and applied a dynamic range using the offset formula. While the pivot talbe grabs the correct # of records, it inserts a blank column and row. There are NOT any blank cells in the data source range, as reflected by pivot table counts for the Blank column and Row. Any idea why Blank is showing up in my pivot table and how I can correct while maintaining the dynamic range?
View Replies!
View Related
Use Dynamic Range In Pivot Table
Im trying for the first time to use a dynamic range as the 'source' for a pivot table. The pivot table and the source data are in two different tabs. The source data is on tab Log! and i have used Daves formula (i hope correctly) to define the dynamic range. The range starts at Column B and is 22 columns wide: Refers to:=OFFSET(Log!$B$1,0,0,MATCH(""*"",Log!$B:$B,-1),22) I have named this dynamic range DMR. I have a pivot table on another tab. When i try to use DMR as the source (Log!DMR), it gives me an error "Reference is Not Valid". I must have the syntax wrong somewhere.
View Replies!
View Related
Dynamic Rows For Pivot Table
I wish to create a Pivot Table. It is based on source data with fixed columns (A to W) and dynamic rows. The following is my Dim WS As Worksheet Dim DataRange As Range Set WS = ThisWorkbook.Worksheets("sheet1") DataRange = Range("A1:W1", Selection.End(xlDown)).Select ThisWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=WS.Range(DataRange)).CreatePivotTable _ tabledestination:=Sheets("sheet2").Range("A1"), tablename:="PivotTable1", defaultversion:=xlPivotTableVersion10 Dim PT As PivotTable Set PT = ThisWorkbook.Worksheets("Sheet1").PivotTables("PivotTable1") PT.PivotFields("Type of Work").Orientation = xlPageField PT.PivotFields("Profit Center").Orientation = xlRowField PT.PivotFields("B/(W) CTD Net Rev").Orientation = xlDataField Because I'm going to use it in my work, it must be reusable. And every time I use it, the numbers of row are different. Therefore, I made the source data into a dynamic range. The range changes when rows change. However, problem always appears in the following line:....................
View Replies!
View Related
Pivot Table Dynamic Range, Last Used Cell Not Same Column
I've got two pivot table reports working off one dataset. I've named the range Recharge with the formula as below.. =OFFSET('Recharge'!$A$1,0,0,COUNTA('ABC Recharge'!$A:$A),16) But this uses column A as the longest column... but sometimes it will be column I - how can the formula be adapted ? or can it be ? i've been looking at the Max function and trying to incorporate that but my limited brainpower has gone to mush.
View Replies!
View Related
Dynamic Refresh Pivot Table Pagefields With Macro VBA
A simple example of the problem: Imagine a pivot table has two pagefields at the top. The first list the gender ("boy" or "girl"). The second one below it lists people's names. How can it be programmed so that when the first (gender) filter is selected to "boy", pagefield 2 (the names) will only display the names of boys. So I would like the pagefield to work like regular multiple filters would. (And not list every possible item for all)
View Replies!
View Related
Set Pivot Table Dynamic Source Range Macro Code
Ultimate goal is to automatically update the source data for 4 pivot tables that are on 1 worksheet. The data for those 4 pivot tables are on 4 different worksheets w/in the same workbook. Consolidating into one dataset is not an option. I'm familiar with a dynamic named range, but the 4 worksheets that contain the data are replaced daily via automated Access output which creates an error. I know how to do this adhoc by matching the pivot table names with their respective worksheets, but there are many other documents with similar layouts where this would beneficial. Below is an example of how I currently update 3 pivots on separate worksheets w/ the same data range which is w/in the same workbook. My proposed changes are below the current. Any ideas on how to return the workbook name as a string...Or am I going about this the completely wrong way...
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
Dynamic Range For Pivot Table :: Error : Range Is Invalid
I am trying to get to grips with the dynamic ranges for pivot tables. I have named a range data and in the refers to section put: =OFFSET(Sheet2!A1,0,0,COUNTA(Sheet2!A:A),COUNTA(Sheet2!1:1)) I was hoping that then when i go to data > Pivot table and it asks for the range i could put =Data but it tells me that the range is invalid. I have attached a copy at the bottom.
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
Dynamic Sorting In Table
Is it possible to have dynamic sorting in a table? What I am after is a scoreboard type effect where as results are entered, the teams are sorted automatically, rather than entering all of the details and then doing a sort afterwards. I have attached a table where I would like the sorting of columns B - E done in the following order (All decreasing order): Comp Points first then by Sets % then by Points % I am drawing this info from another table. Will this have an effect on if it can be sorted dynamically? Hoping that as i enter a result in the other table, it will bring across the current comp points, set % and points %, and then sort the table into the rankings all at once.
View Replies!
View Related
Navigation Of Dynamic Table
I have a large table where rows are added by copying either the top row or the bottom row and adding it to the bottom of the table. Below is the coding I was hoping to use to jump from cell to cell within the rows using a hotkey assigned to the macro. Is it possible to apply the following code in a way that copies the same hotkey targets from row to row? In other words, a row copied from row 1 to row 2 would still have functioning targets of A2, D2, L2 and Q2. Select Case ActiveCell.Address Case Is = [A1].Address: [D1].Select Case Is = [D1].Address: [L1].Select Case Is = [L1].Address: [Q1].Select Case Else: [A1].Select End Select
View Replies!
View Related
Constructing A Dynamic Table
able to create a table that is derived from a pivot table (see attached file). The pivot table represents sales amounts for each company in each period. And can be drilled down on country and category. The table that is derived from this pivot represents the market share percentages. In the attached file in filtered sales data from country "UK" and the pivot table only shows the active companies in this market. However in the derived table all companies are still represented but inactive companies are now represented by "#REF" values. Is there a way to exclude inactive companies in this derived table? If so, how can i make this derived table dynamic?
View Replies!
View Related
Populate Userform From Dynamic Table
I have a userform to be populated with data from a dynamic table for purposes of showing users their daily stats. The data is populated on the worksheet in the following way- .............. When the userform is pulled up, it needs to show the following details - UserName - User1 Workitem 2 : 12 Workitem 3 : 7 As the work items go all the way to 65, I wanted to only show the work items that had actually been worked on by each person - What I am struggling with is how to populate the textboxes on the userform with this data - without getting into an absolute mess of if statements - If Range(WorkItem1Range).Value <> "" Then userformStats.WorkItemA= Range("a2").Value..........
View Replies!
View Related
Sort A Table Of Data That Changes. Is Dynamic
am working on a spreadsheet that requires sorting a list of names and numbers. The numbers have a sum formula attached. I can set up the macro to sort the list fine by selecting the appropriate cells and creating the macro. The code for this is below Sub SurnameSort() ' ' SurnameSort Macro ' Sorts by mechanic surname ' ' Range("A10:H13").Select Selection.Sort Key1:=Range("B10"), Order1:=xlAscending, Header:=xlGuess _ , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub However, I want the macro to still be able to sort the data when I add new rows to the list later on. In other words, the code above in bold somehow needs to be modified so that "H13" extends as far down as needed.
View Replies!
View Related
Dynamic Link Between Table And Chart
I m trying to make an Excel Charts that would update according to the choice made in a control box. I have uploaded the example test.xls. If you open the file, you ll see the graph is link to product 1. What I d like to do is if I choose product 2,3 or 4 from the control box, the chart updates itself to the good corresponding product.
View Replies!
View Related
Summary Report Of Dynamic Table Data
I am interested in finding vba code that I can enter as an add-on for a weekly training report that I receive. An example of the weekly report is attached. A Computer based training program populates the reporting tool with the date that the course was completed. Each week a report is generated as attached with the dates completed filled in the matrix. The goal is to: Report the total percentage of training completed. I would like to be able to run a vba add-in that will determine what rows have entries. Perform a countA on the date fields. Sum the counta's and populate a cell with the % complete. In one simple touch of a button or keyboard function that can be ran each week without editing the code. These reports are filtered by department and the size of the matrix change all the time. The hang up I am having is making the code dymanic enough to figure out what cells are filled and calculate and populate by that factor.
View Replies!
View Related
Macro To Sort Dynamic List Into Table
I have a list of letting agents that I need to sort into a table to import into Access. The list is dynamic in that not all the fields are present for every record. I will have over 4000 records to sort out, so I would really like to automate this. If possible the macro should read rows from the data worksheet and write to columns and rows on the Table worksheet. I have attached a small example of the data and output required. To make it a little more challenging the column header name is part of the data and will need stripping out as well.
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
Multiple Match Values - Index Dynamic Table
I'm trying to extract the selected suppliers orders from a 1000+ rows list ; like appearing with the example of supplier "D" ; and the new one should be dynamic , it means if any data added to the source orders list and if the supplier was one of the selected the new table automatically update ; or can be refreshed
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
Dynamic Range For Table With Unequal Used Rows In Column
How would I select a dynamic range in VBA without using name a range in Excel. I have searched for possible answers, however they use xldown, or xlup which works great for non-empty cells. My problem need to select the area where there are empty cells. Here is a snap shot of the data looks like: Trade Date A B C D E 2008-Feb-11 450,432 2008-Feb-13 2008-Feb-15 The cell with number is where I got the formulas. Now i wish to fill that formula with the rest of the area. However, the problem is the number of rows and number of columns may change in the future. So I need to select those empty areas in a dynamic way.
View Replies!
View Related
Subtotal Rows & Summary Report Of Dynamic Table
writing a VBA to convert a set of data in sheet 1 to one in sheet 2(I am enclosing that as a Excel document"Test -Original").I have described what needs to be done ( step by step ) below. Develop a macro 1. I have a report from SAP BW, the original format of which is in sheet 1.I need to develop a macro using VBA and need the report with the format in sheet 2. 2. I need a “Results “row after every Bill to Party in column A as below(screen shot 1.doc): The number of customers is dynamic i.e.it keeps changing every month 4. Nothing needs to be done to column.SAP BW will not overwrite the format and the data in column G.Hence leave it as it is. 5. Calculate the number of Sales document numbers for each customer and put the value of 1 for every value. If it is blank it should not be counted and put the value of 0 for those rows. (Shown below) Display the sum of the number of sales document numbers in the results row for column H 6. In the column I, put the value of 1 if the difference column (column G) is 0 and put the value of 0 if the value in the difference column (column G) is any value apart from 0.now sum the value in the results row for each customer and display the summation value in the results row under column I 7. Compute the percentage which is the values in (column I/Column J)*100 .This should be done only for the results row
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
|
TRACKER
