Excel 2007 :: GET PIVOT DATA With Variable Parameters
Mar 2, 2012
I have a pivot table that has several row labels and 1 column label. I am trying to create a Ranking table outside of the pivot table because I am using Excel 2007 and can't use the new Rank option in Excel 2010. My difficulty is in being able to rank each subgroup of the row labels.
I have thrown a column for "Target Rank" in the data, just to validate my calculation in the PivotTable worksheet, but it is only correct as long as all Company and Product Name options are selected for a given Sex, Age, and Scen Defn.
My non-dynamic formula is:
=RANK(GETPIVOTDATA("Target",$G$5,"Company","AIG a","Product Name","Elite Index UL a","Sex","F","Age",25,"Class","1-Best NS","Scen Defn","Overfunded A"),$L$8:$L$30).
I have figured out how to make some of the items variable to this degree:
I know can make the $L$8:$L$30 copyable across columns by changing it to L$8:L$30.
My dilemma is several fold.
1. If I filter the Company or Product Name the $30 in the range no longer applies. Is there a way to make the bottom of each grouping dynamic?
2. Is there a way to make the item entry associated with the "Class" column label a variable so I can copy across columns?
3. Similar questions related to the row labels for Sex and Age?
4. Lastly, I can write an expression that would make my Rank columns blank if the Company column was blank, but is there a way to reset the data range to look at the next block? That is, if "Age" changed (was not blank) that would indicate the top of a block of data.
I have tried to think of ways to use INDIRECT and I don't know VBA well enough to do it in a macro.
ClassValues1 - Best NS4-Nonsmoker1-Best NS4-NonsmokerTarget RankAV 5 RankAV 10 RankTarget RankAV 5 RankAV 10 RankSexAgeCompanyProduct NameScen DefnTarget Target RankAV 5AV 10Target206420125F25AIG aElite Index UL aOverfunded A 1,520 20 12,848 33,607 2,135 9181961618Allianz LPLife Pro+Overfunded A 2,796 9 10,613 26,147 2,847 7221892217American GeneralElite Global PlusOverfunded A 2,810 7 7,930 26,323 2,810 1913617198AmericanGeneralChoice Index GULOverfunded A 1,610 19 11,388 31,409 2,205
I am attempting to create a template that will utilize data from an Access table, and allow the user to select the parameters in the Excel file. The amount of records in this table is slowing down the Excel file, and I would like to lessen the amount of data that is imported based on the user selection.
The Access table will have three columns:
Part Number|Line|Catalog Code
I would like to import all three columns, but allow the user to select the records imported based on the catalog code.
The users do not have read or write access to the sql tables, so I run an Access query at the end of each day to create an Access table that they can access.
I need to combine AHK and Excel, and in this I came up with the solution to save my parameters in a file, and let the workbook autoload it when I open the workbook. To do this I need to pass a command line parameter, or working directory to the workbook, as I will be using many different work directories for this to make sense.
This is only a matter of how to execute my workbook so that the path is included (and usable) to the working directory
I have tried
..workbook-name.xlsm /p "workdir"
But it results in a "grey" version of Excel aka. Excel is open, but no workbooks are.
I have also tried
excel.exe workbook-name.xlsm /p "workdir"
But this did not work either, and is not an option as we use both 2007 and 2010 at my workplace where this is intended to be used.
I can do it very easily by using absolute paths in the Excel auto-load, but as I have explained already this is just not an option.
Scenario: I have a worksheet full of ~360 tasks. Tasks are broken down into phases, have owners, and have status'. I leverage a pivot table to easily sort my data by owner, or by status. When reviewing the pivot table with others we want to update the raw data. This entails changing worksheets and scrolling to the task in question.
I'm looking for an easier way to update the raw / original data.
Options I've tried to pursue but have come up empty: Option 1: Excel modification? Is there a setting in Excel 2007 that would allow me to change a value in my pivot table and have it propagate to the original data set (another Excel worksheet)? So far the answer seems to be "no". Is this possible?
Option 2: Hyperlink from Pivot Table to Raw data field. - I cannot create hyperlinks within PivotTable data cells. - I have used the "hyperlink()" function OUTSIDE of my pivot table, along w/ a "match()" on a key field in my pivot table to create hyperlinks back to my raw data... But this is prone to breaking. - Is there a way to enable hyperlinks from within a Pivot Table? I have seen other requests from individuals that had a pivot table with actual hyperlinks (e.g. [URL]...) and they could not 'activate' them.
Option 3: Tool Tip VBA Form w/ Link?
- If I can't create hyperlinks directly in the table, then how about a function so then when i click (mouse over?) a pivot table cell a form appears with a hyperlink back to the source data?
Option 2 and 3 Assume that there's an way to get at the underlying location of the raw data (e.g. "Pivot Table Worksheet!E10" comes from "RawData!C4". I've dug through some VBA documentation at Microsoft.com but came up empty. I know this link must exist behind the scenes - In Excel 2007 when you mouse-over a pivot table cell Excel displays: "Row - Phase - Owner - Status", which is directly out of the raw data table/worksheet. [[This was an incorrect assumption... When i looked at the pivot table tool-tip closer I realize it was just walking through my data :'( row.
What is the VBA Code to revel the source cell? If i can get at that data, (and hyperlinking can be turned on w/in the pivot table...) then I can easily write a script to walk through the pivot table and create links back to the raw data... Not a perfect solution - but it takes away the need to search through the source data for the row I want.
I Wrote a code which as intended to open each excel file in a folder and copy the data containing in it into a new sheet.
But While running the code the first excel file gets open, and an error message "Run Time Error 91-Object Variable Or With block Variable not set Error"
How to set the file which got opened from the folder to wbk variable.
Code: Sub dataintoonesheet()
Dim i As Integer Dim jk As Integer Dim j As Integer
I'm trying to call another sub with a variable string. I'm using excel 2007. I get the error "Compile Error: Expected Sub, Function, or Property" on the "Call" code. The want the code to run multiple different subs based on user input. For example if they typed "this" it would run the t sub, then h sub, then I sub and finally the s sub.
Code example
Dim Uncvrtdtxt as string Dim SubName as string Dim i as integer Uncvrtdtxt = "this" For i =1 to Len(Uncvrtdtxt) SubName = Mid(Uncvrtdtxt, i, 1) & "Route"
how to create a custom function with an unknown/changing number of input parameters?
Specifically, I need to create a function that will calculate the average of 1,2 or more ranges. I can't use the built-in average function as I then need to use the average in my function.
what I have so far is:
function points(current as range, previous as range) if previous > current then final=1 if previous < current then final=0 if previous = current then final=2 points=final end function
This works okay if I only only have 1 range of values. but in my worksheet I need to be able to enter =points(a1,c1:f1,g1) and have the function calculate the average of c1:f1,g1 and compare it to a1.
i make a lot of "trending" files at work where i link a cell to a seperate workbook where all workbooks are in the same folder.
these workbooks all have the same name with the date at the end
example: workbook 06-01-2011 workbook 06-02-2011 all the way to workbook 12-31-2011
usually i use find replace to change the date one at a time, but doing that 365 (x how many cells i have referencing) times is redundant
i have seein INDIRECT of the cell changes but nothing for the workbook name chaning
here is what i am trying to do: ='C:File Path[WorkbookX.xls]Sheet1'!A1 where the X WorkbookX is variable (the date at end of file name)
i have a list of all workbook names or just the dates and would like to do something like:
=D1&$A$1 where D1 is the full path name or ='C:File Path[Workbook"D1".xls]Sheet1'!A1 where D1 is the date name
using excel 07 i prefer not to use a macro for how i am doing this but if a macro is the only way then i cannot have it actually open the files to pull the data. i prefer no macro since i set it up for future dates so when the new file is created it automatically updates the reference.
my end result i usually have something like A1=referenced cell from first workbook, A2= cell from 2nd book, etc. sometimes B1 would = a different cell from first workbook and so on if i am trending multiple cells from the workbooks
I am attempting to pass a boolean variable between two macros. The SUBMIT macro calls the STATUS macro to do some verifications and the STATUS macro should return the boolean variable RDY back to the SUBMIT macro, but does not.
I am using Excel 2007. Both macros are in the same Module. The STATUS macro is called from several places in the code.
Public Rdy As Boolean Sub Submit() [code] Call Status If Rdy=True then msgbox("You have completed your form") else msgbox ("Not ready") [code] end sub
I have a data table that contains 180 dates e.g. 9/30/06, 10/31/06 etc. When I'm creating the pivot table via a macro I'd like to have (2) parameters to enter at run time, those being Beginning Date and Ending Date.
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?
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..
I have a pivot in excel 2007 but I have the following problem; I want to exclude values from one variable that have a value lower than zero or blanks:
Month #AVG Employees Jan 200 Feb 171
But the problem is, if i filter on the variable by placing it in the Report filter (select multiple items to remove blanks and -999), when i refresh the pivot next time for the new month it won't add the new values for the next month. How to have the filter so it applies the criteria but allowing new values in for the next month.
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 ...
This is a macro-enabled workbook for the Board of Education. The workbook is 31MB and located at [URL]. Click on View on the top and then look to the right on the ribbon. Click on the Parameter Selection icon.
Parameter Selection.jpg
This allows the user to select the entity to examine. If they do not select an ISD in combobox1, it should default to everything. If they select an ISD like Oakland Schools above, but nothing in Combobox2 than all School Districts will be included and so on. Once they have made their selection, they click OK. Now, what hopefully should happen is the data for their selection should be pulled from the “Combined” tab and put in the Pivot Tables on the Working Pivot Tables tab. Once that is done, another function should take the data from these Pivot tables and “refresh” the graphs and charts on the Summary tab at the front of the workbook.
One thing I’m still trying to correct is the data that populates this workbook comes from a web site that must be downloaded manually (don’t ask). It comes down as csv. You can see from the image above the “code” columns should be 5 characters with leading zeroes. Unfortunately, the source data is not that way. I have saved the source file as xlsm, formatted these code columns as custom since text won’t work and imported them into the workbook. The data moved over to the Pivot Tables (manually done to this point) does not maintain the leading zeroes.
I am new to doing advanced stop with pivot tables. I know how to make one to do very basic things but I have since learned you can add your own calculated fields. I have a list of data that has a line of record for each different day of sales which each guest. There is a lot of information on the sheet but the columns that matter are as follows:
ColA= CustID
ColB= Trips(this field is the same value for each record of the same customer and represents the total number of trips the Cust has) on a side note this field can be removed if it would be easier to just do a count of custid as those are the same thing
ColC= Months(this field is the same value for each record of the same customer and represents the total number of different months the cust made a purchase)
ColD= AmountSpent
I can use a pivot table to show me the total amount spent by Cust real easy. But I would like to add in the two other fields Average per trip and average per month by cust. Is this something that can be done in a calculated field?
I use Excel 07. I know I can run formulas to get the answer I want however due to the shear volume of records being over a million(I know we should use access at this point but the company does not want to) the time it takes the formulas to calculate freezes my computer(need a serious upgrade).
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?
I 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.
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 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.
