How do I create a Named Range that is dynamic (I think using the OFFSET function) that automatically expands and contracts as the rows and columns of my data changes?
Next step, I then want to use that Named Range as the source of the data for a PivotTable.
Yes, I know starting in Excel 2007 you can use Tables and don't need to create a dynamic named range. That is not an option at this time.
I have:
Excel 2003ABCD1StockDatePriceReturn2MSFT31-Dec-1027.91-6.97%3MSFT31-Dec-093054.32%4MSFT31-
Dec-0819.44-45.39%5MSFT31-Dec-0735.619.22%6MSFT29-Dec-0629.8614.19%7MSFT30-Dec-0526.15-2.13%8MSFT31-
Dec-0426.72-2.37%9MSFT31-Dec-0327.37111.84%10MSFT31-Dec-0212.92-21.98%11MSFT31-Dec-0116.5652.77%12MSFT29-
[Code] .......
and would like:
Excel 2003ABCD1Sum of ReturnStock2DateBAINTCMSFT331-Dec-970.83851.14150.5659431-Dec-98-0.33350.68793.2921531-
Dec-990.27041.77732.3668629-Dec-000.59270.4606-0.6286731-Dec-01-0.41240.04620.5277831-
Dec-02-0.1493-0.5049-0.2198931-Dec-030.27741.05841.11841031-Dec-040.2285-0.2702-0.02371130-
[Code] ...........
Code:
Sub codedPTable()
Dim pt As PivotTable
Dim strField As String
Dim WSD As Worksheet
Set WSD = Worksheets("Data")
[Code] ..........
Only prints this:
Excel 2003ABCDE1Sum of ReturnStock2BAINTCMSFTGrand Total3Total1.99894.69867.331214.0287Sheet2
If you have a more compact macro than what I tried to change above even better (the data is in the simplest testing format).
I've a macro that generates a pivot table from another excel workbook, however i need the range to be dynamic as the excel workbook im generating the pivot table from changes in number of rows on a weekly basis. Here is the existing coding:
Code:
Sheets("QA").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"QA!R1C1:R22580C12", Version:=xlPivotTableVersion10). _
CreatePivotTable TableDestination:="Pivot!R3C1", TableName:="PivotTable2" _
, DefaultVersion:=xlPivotTableVersion10
how to make the SourceData dynamic
I have a sheet in my workbook called Results. This sheet contains a list of a fixed set of columns (A - N) but with a variable number of rows.
I am trying to create a macro to create a pivot table from this data, which automatically selects all the rows in the Results sheet to form the data set.
I've had a look at some of the other threads on this topic, but just can't get this working.
I have the following code that I have pieced together from other threads - you will see I am trying to set "PivotRange" as the data set and include this as the data source in the pivot. I get a subcript out of range error when I run this.
Dim PivotRange As Range
PivotRange = Range(Worksheets(Results).Cells(1, 1), Cells(lastRow, lastColumn))
Sheets.Add
[Code].....
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 9 Replies View RelatedIm 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.
I have a pivot table based on a large dataset within Excel itself. The dataset looks as follows:
View 9 Replies View RelatedI 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.
I've created a pivot table and I'd like to index through each "value" in the page field and then copy the results to another sheet, one sheet per field returned.
I can't figure out if it's possible to index through the list though. Is it possible?
I am trying to create a Chart from a Pivot Table. I want the Chart to show my projects and present each months amounts side-by-side comparing (Plan, Actual, Forecast) data each month for the calendar year.
I am trying to figure out how to generate the pivot table showing multiple projects so that the data is grouped by month comparing (Plan, Actual, Forecast) data. Projects are listed down the Rows while months Jan-Dec are along the columns. Each month has 3 columns because the second row contains header for the data sets of Plan, Actual & Forecast. (see sample below) When I try to create a Pivot Table, It shows multiple month fields (Jan, Jan2, Jan3, Feb, Feb2, Feb3) instead of a single month.
{Using Excel 2003}
NameOwnerJan JanJanFebFebFeb
PlanActualsForecastPlanActualsForecast
Project 1Region 1 126.1 119.6 119.6
Project 2Region 2 18.0 0.9 0.9 8.2 8.2
Project 3Region 2 80.0 2.6 2.6
Project 4Region 3
Project 5Region 3 60.0
Project 6Region 4 55.8 55.8 55.8
Project 7Region 4 19.4
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.
I am trying to create a pivot table on a new sheet names as 4x4
I am getting following error:
Error : 1004
Reference Not Valid
I am not sure where is the problem. I have generated this code by using macro recorder and just changed the source and destination of the pivot generation 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...
Is it possible to aggregate data in a pivot table from different categories?
I have excel 2003.
For PURE illustration, I have 6 columns, A-F, respectively:
"TV Show", 4 columns for names of people who watched the show (Persons 1, 2, 3, and/or 4), and finally, the duration / "Time" of the show.
I want to see in a final output:
Anytime a person has watched the show (whether i have penned him in columns 1, 2, 3 or 4), Excel to aggregate the total hours watched by that person.
When i try to do this with my pivot table i run into an error: if Person A watched "TV Show X" in row 1 and his name is in the Person 1 Column, Excel will not aggregate his TV time with "TV Show Y" in Row 2 when his name is in the Person 2 column.
It will sum up the categories separately even if the "Person" inputs in the separate "Person" columns are exact matches.
I am running Excel 2003 on a Win7 system.
Here is my situation:
Each of my data sets spans roughly 75 columns by 250 rows at present, but this could expand. The first 7 rows contain metadata. Columns 2-25 or so contain the raw data, from which everything to the right is calculated. The data sets have most columns in common, but not necessarily all.
In order to tease out the most meaningful information from my data, I frequently sort all or part of it based on varying criteria. When I find a useful sorting criterion, I create a new column with a header that describes the criterion and populate it with a formula that returns a 1 if the condition of interest is met for that row, or a 0 if it is not. For example, if I am doing this in column AA, I might enter
=--(AND($AX8>$AA$4,$Y8>0))
and copy it down to the end of the data. The resulting vector of 1s and 0s quickly re-identify data that meets that criterion even after subsequent resorting. It also makes locating data that meets multiple sorting criteria extremely simple. Essentially, I create a truth table.
Cell $AA$4 in the above example contains a "comparator" value I might wish to change at some point, which would change the subset of data the condition selects for.
Here's the first hard part:
For each data set, I need the ability to generate meaningful plots that includes separate series based on the criteria I have described. However, I also need to retain the ability to resort the data or change the comparator value without disrupting these plots. In other words, the plots must NOT change when the order of the data is changed, but MUST change to display the appropriate data when the comparator changes.
Here's the 2nd hard part:
Once I have this working for one data set, I need to be able to port it to other data sets (which are contained in other workbooks), so that I can compare equivalent plots from each. I also need to minimize the number of manual steps involved in doing so, to avoid human errors and excessive time consumption.
The only other possible complication I can think of at the moment is that, to this point, I have been inserting blank rows to isolate subsets that I do not wish to perform further sorting on from each other.
Right now I am angling toward VBA code that loops through the entire data set to generate base dynamic ranges using the column header row (row 1) as the names, and the entire column of data for the rangeloops through the truth table columns to generate "branch" row ranges for each of the sorting conditions,loops through the entire data set one more time to create "branch" ranges for each of the base ranges.
I could generate some code to accomplish a one-off solution for a given configuration of a single data set (provided there is not a list length limit in a chart series that I'd be violating)...but without a dynamic named range, I don't know how to get to something that would update appropriately. So in essence, I am still stuck at the dynamic range part of this.
I am trying to insert a Pivot table with dynamic Name Ranges. It needs to start from Cell B1 as Column A has hidden formulas in them. I created an Dynamic Name range and tried to insert a pivot table. Excel then throws out a error stating "Data source reference is not valid". I tried re-saving the document but still no luck.
View 1 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.
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.
I am using a pivot table in excel 2010. 15 columns (fixed) and plenty of dive downs for rows.
I am trying to 'hide' all of the rows containing values less than 10. If it is easier we can start with hiding values of zero and go from there.
It is key to hide the rows as within the dive downs the rows within the pivot are to many to sort through. If there is a way to do this using the grand total columns for each row that would work as well. Just to be clear I need to hide the entire row not just report the empty cells as zeros. If any further detail is needed feel free to ask.
For example: If value of column O "Grand Total" = 0 'hide rows with value 0' ELSE 'display rows with value greater than 0'
how to find the source data range of a pivot table (that already exists) in Excel 2007?
View 1 Replies View RelatedI have an SQL Server that has a query (a view stored on the server) which takes roughly 10-20 seconds to execute. This execution time is the same in Excel 2007 if I import data to worksheet/pivottable. It is also the same in Excel 2003 while importing data into a worksheet. It returns about 350 rows.
However, if I try to make a pivot table in 2003 based on this query, it is much slower. I can set it up fine, but refreshing this pivot table takes at least 10-20x as long to complete, in the order of 5-20 minutes, to sometimes taking so long that I give up and kill it by hitting escape a bunch.
It's important to note that I am forced to used Microsoft Query to set up a pivot table in Excel 2003. There is no other way, I believe.
While it is running, Excel just sits there saying "Waiting for data to be returned from Microsoft Query".
Here is the weirdest part. Once the refresh starts, Microsoft Query loads in the background, you can see it on the taskbar. I believe that is expected behavior, but if I click on MS Query and view its datatable, I can see all the data is sitting there.
So what is the hold up? MS Query has the data. Excel is waiting for it to be returned. What is going on? And remember from above, if I do a simple import data to worksheet, MS Query is not used and everything is fast.
So my question is does anybody have any tips/advice on this issue? I need a way to not use MS Query for a pivottable's data source or I need a way to make MS Query not freeze up. I don't get why MS Query needs to be involved in the first place since I am not doing any parameters or so forth.
I have a set of four pivot tables on a sheet that I need to programmatically change a Report Filter (Page Field) so I can create sets of reports in an automated fashion. This will be the first step in that process. The change will involve choosing > 1 Role each time the code loops through based on Named Ranges I've defined that are associated with that Role.
My code thus far:
Code:
Sub TestCode()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
[Code]....
emm_dc_gsr is one of many Named Ranges that will contain a variable number of elements. Just using the one right now to see if I can get the code to work, I'll eventually make another Named Range/Array of all them so I can loop through each Report ("ReportPick").
I want the Report Filter to consult that Named Range for its values and apply those values to PivotField "Role" that is used as a Report Filter.
When running this code above, I get a "Role" Field that says "All" but no values (the table is completely blank), with no evidence as to why it'd be blank (all filters in every Report, Column and Row are working normally and are filled in). When I choose a value manually after the code is run, the pivot table values populate. Do I need to somehow index the Named Range in that loop? I'm just confused about this step right here:
For Each pi In pf.PivotItems
If pi.Value = RolePick Then
pi.Visible = True
Else: pi.Value = False
When I've run other versions of the code, I've gotten an array version of it to "work" using LBound and UBound, but it never chooses the right two values even though those are verified as stored in the array via a pass-through. It chooses the first few values in the Report Filter.
Here's the corresponding code for that:
For i = LBound(myArray) To UBound(myArray)
pf.PivotItems(i).Name = myArray(i, 1).Value
pf.PivotItems(i).Visible = True
Next
I do not care if I use an array or a Named Range. I just want something that is simple and works. Passing the values directly from the named range seems easiest to my brain, but I'm open to anything and I'm clearly missing something (probably silly).
I also have no idea why " .AutoSort xlManual, .SourceName, .EnableMultiplePageItems" is necessary though every piece of sample code I've seen seems to have some variation of it.
(Using Excel 2010, Windows 7.)
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?
I have a range of data that will grow as the days of the month pass but I need to run calculations on this range. The data is 13 columns wide but the number of rows will increase daily. I remember using a formula in the Define Range that would automatically take into account new additions using the OFFSET function but cannot for the life of me think how exactly to do it.
View 4 Replies View Relatedi have a database of the kind:
Model Version Color Store
1 a zz HH
2 b xx QQ
3 c yy OO
4 d ww PP
I need to obtain in the y axis the model, and in the x axis the store.
The data per each model and store should be: the version and color.
I'm using Pivot table to do that, but i'm only able to obtain the "count" (or sum or other formula) but I'm not able to obtain the content of the cell.
Example:
Store HH PP
Model
1 Version a d
Color zz ww
2 Version b c
Color xx yy
I have a table with outstanding days for Funds owed by multiple accounts which dynamically changes everyday I am looking to group this table by >5days, 6 to 14 days , 16 to 40 days ,>40 to 90days and >90days consistently even though the table will change dynamically every day..
View 4 Replies View RelatedI am trying to make a dynamic pivot table using VBA. Unfortunately, Im not very good.I have a set of data with variable number of rows and variable number of columns and I need to make a pivot table. Need to select this data from sheet - Master, where the first data entry starts in cell A1. of the data in the sheet, i only need the name and age title In the pivot table fields -
ROWS needs to contain - name
COLUMN needs to contain - Age
Values need to contain - count of Age
After this is done, since the data will be dynamic, the pivot table will also be dynamic. i need to copy the pivot table data and paste it in another sheet so that I can do some filtering. The filtering part I think i can do my self.
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:....................
I'm using Excel 2003.
I have a Userform where you can enter a date and select a comparison operator from a combo box. Any one of these (= < <= > >= <>)
In VBA I then load a range into a dictionary object and then loop thru it to compare the dates in the dictionary to the date selected in the userform but I can't get the dates to compare correctly. I think it has something to do with the Evaluate function comparing the dates as strings but I'm not really sure.
VB:
For Each strKey In dictValid.Keys()
If Not Evaluate(CDate(dictValid(strKey)) & cboDteOperator.Value & CDate(txtDteSel.Value)) Then
I have 3 test scores for each student. Currently, I have 3 students.
For example :
John 95 90 85
Cindy 50 60 100
Dan 87 86 90
I have a chart that that plots 3 lines, one for each student.
However, I want the chart to dynamically update if there's another student. In other words, I would like the chart to add another series while the legend updates to include another student.
I am trying to do this in Excel 2003.