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.
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 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.
Excel 2007
No empty rows
No empty cells
No calculations in cells
Field names look fine - no punctuation no merged cells
If I select one column - any single column I can generate a pivot table but not with multiple columns
I used the vba recorder to get the code and didn't change one thing, and now I am getting an error when i click on the command button to execute the code.
error
Run time error 1004
The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By Box isn't the same or blank.
Row 1 = column headings
row 2 is the first set of data, *****, however, most of the data in the column is blank. There are only 2 options, (Yes) or blank, and I am trying to sort the sheet so all the (Yes) appear at the top
when i did the macro recorder, it seemed to work.
I am having problems in Excel 2007 using hyperlink formula to jump to a different worksheet in the same workbook.
Filename: Model Variables.xlsx
Worksheet to seek: Org Structure
Cell to seek: A1
Formula tried: =HYPERLINK("[Model Variables.xlsx]Org Structure!B4", "My Cell on different sheet")
Error: Reference is not valid
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've been trying to create pivot table in excel 2010. Unfortunately I've been getting this message "Data source reference is not valid"?
View 2 Replies View RelatedHow 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'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.
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...
I have seen one of your posts in which you make a graph update based on dynamic named ranges. Here is my problem, I already defined the name of my ranges as:
ChurnDiario =OFFSET(Clientes!$E$25,0,0,1, COUNTA(Clientes!$E$25:$AH$25))
Ejex =OFFSET(Clientes!$E$8,0,0,1,COUNTA(Clientes!$E$8:$AH$8))
The issue is that when I tried to enter the values and category (x) axis with:
Values: =offset(ChurnDiario,0,0)
Category (X) axis labels: = offset(Ejex,0,0)
On both, excel replies “That function is not valid”. I’ve tried, but still can’t make it work.
I want to reference a table (not a pivot table) from another Excel file. I want it to update automatically if the original table is changed in any way.
View 3 Replies View RelatedI 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 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 have a pivot table without report filter and I set insert page break after one of my selected row labels.
I also would like when I print, these row Labels become the title of these pages at the same time.
Actual File : Untitled.png
However I would like when I click on print or save as pdf will print all pages as one file and each Center that I have set page break for, in separate page and add that specific center as its title:
Attachments for more details:
test_Page_1.jpg
test_Page_2.jpg
test_Page_3.jpg
test_Page_4.jpg
I am having issues with a VBA script. The Spreadsheet is linked to a SQL DB and updated with data from scripts that are executing throughout the day. This is then fed to a dashboard. We have a Baseline that shows in the Date column as 0/0 and 1/1. I need a way to ensure that the 1/1 is always in the last row of the chart so the baseline shows across the entire range of dates. The current method is to declare a variable and add 1 to it whenever we iterate through the sort for the other dates. However everyday the 1/1 baseline column ends up further to the beginning until it is next to the 0/0 in position 2.
So I am trying to find a way to have the macros find the last row of the Date field and then set the position to be equal to the last row.
I am using pivot tables/charts for drilldown reporting. After running a complex macro to consolidate multiple reports into one range, I need to generate pivot tables and charts.
These reports will change month to month in the number of entries they have (template will be identical), and therefore my data source range for the pivot table is bound to change. Therefore my question is, how to develop a macro that will dynamically change the source data range to the amount of rows?
I'm trying to take data from multiple worksheets and copy them into a summary sheet in the same workbook, and then make a pivot table from the summary sheet.
I got the first part done, but I can't figure out how to make the pivot table to incorporate new data in the summary sheet as new worksheets/data is added.
I have a pivot Table with region & product wise as shown in the enclosed file. While calculating % of each item in region it is referring to grand total or total of column. Is there any way how to get this % calculated in regard of subtotal region.
View 7 Replies View RelatedA 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 2 Replies View RelatedI have a sumproduct function that is aimed at a big block of data. Is there a way that I can write the function to where it will always find the last row of the data set and adjust accordingly?
For example if right now the range is A1:G2000 and next month I add 20 rows of data I want the sumproduct ranges to automatically adjust to A1:G2020.
I have two fields where users enter a security identifier (cell P1) and the declaration date (cell P2) and i'd like to be able to pull all the records from the master table (A1:K10) where the security id matches (column H) and the request date (column C) is <= the declaration date (i've attached a sample file for your reference). I tried using a pivot table but it doesn't let me filter for "less than or equal to" values for the request date.
View 2 Replies View RelatedI have made a pivot table which I use each month by just updating the source data, however I was trying to have a cell in another workbook say "=A31" but it keeps going "=getpivotdata(...specific name".
My usual practice was to copy the top ten rows of my pivot table into another file but I am trying to erase this step.
I have a workbook that reports data on a daily basis within a month. I receive data daily from a different workbook and import the data for the day in its correct worksheet which has functions that automatically calculate everything I need, which then gets collected from a central worksheet which accumulates the "Total".
However, I have recently added 2 new pivot tables to my analysis, and apparently when i copy/paste the pivot tables to each new sheet, the data source is references the original sheet where the pivot tables come from.
I tried doing a dynamic name range, and that states the worksheet name in its reference as well.
How can I make a Pivot Table, gather the information from the same range $A$1:$J$5000, but only for the sheet in which the Pivot Table lies.
The sheets are labeled by the day of the month.
So, data for February 1, 2009 is worksheet "1"
February 14, 2009 is "14"
without the ""
