Getpivot data dynamic reference

Hi all,

I am using the getpivotdata to retrieve data from a pivot table,
the formula contains references to cells $C$4,$D$4 for different items
of the fields of the pivot table, where the user can choose via a
dropdown menu the values.

=GETPIVOTDATA("Sum of Volume 1",'Summary 1'!$A$4,"Month",$C
$4,"Country",$D$4)

Would it be possible to do the same for the data field "Sum of Volume
1" and the pivot table name  'Summary 1'!$A$4 as I have different
pivot tables.

I tried the following formula for the pivot table name:
=GETPIVOTDATA("Sum of Volume 1","'"&G4&"'"&"!$A$4","Month",$C
$4,"Country",$D$4)
where G4="Sum of Volume 1"

but excel gives me #ref.

any idea?

thank you
0
Nicawette
2/5/2010 2:01:18 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
1053 Views

Similar Articles

[PageSpeed] 15

Excel 2007 PivotTable
GetPivotData with drop-down lists.
http://c0444202.cdn.cloudfiles.rackspacecloud.com/02_02_10b.xlsx
0
Herbert
2/5/2010 5:59:45 PM
Reply:

Similar Artilces:

Data validation
Is there a way to have Data validation display a description next to the data, but only insert the data into the activecell, similar to a Combobox? For example display 1 Groceries 2 Car 3 Medical but insert 1,2 or 3 into the cell. Thanks Laurence Lombard Hi Laurence this can only be done with VBA. See http://www.contextures.com/DataValNameID.xls for an example. To give you a short overview you have to do the following: 1. In your data list for the listbox add a concatenated column of your number and the name 2. Choose this concatenated column as data source for your list 3....

How to reference a active column in excel-tricky
I know the basic paste in excel with active reference, however I ge stuck when I want to paste columns that will reference every thir cell. A simple example: ---------ColumnA ColumnB ColumnC ColumnD ColumnE ColumnF Row1: 5-------------A--------30---------C---------60---------E I want to put cell on another spreadsheet that will be right next t each other that will calculate A5/6, C5/6, E5/6 etc But these cells are next to each other so if I copy the first one i gives me A5/6, B5/6, C5/6 etc. Basicaly how to tell excel that these cells that will calculat something should reference every...

data from Picklist
Hi, I'm looking for put the datavalue of a picklist in the field, when the ONSAVE is called I've try this : crmForm.all.salutation.DataValue = crmForm.all.new_genre.DataValue; But the return is not the datavalue but the picklist index ?? i think datavalue from the picklist is not the good properties for me, can you give me the good properties from datavalue please ? Thank's. Hi I am not sure what you are trying to achieve. However when working with a picklist, I usually use crmForm.all............SelectedText. Regards Mark "Jean-Luc" wrote: > Hi, > ...

Entering Data to make a list?
Is there a way that when I enter something into A1 &B1 it will fill down on a List? Like if I type apples in A1 & $12 in B1 on a certain sheet it will copy Apples and $12 , then if I type Pears & $5 it will copy to the next blank cell and so on -- Thanks in advance for your help....Bob Vance .. .. .. .. Bob I'm assuming you have this list on another sheet. This little macro will do the following: When you enter something in B1 of the first sheet, it will copy A1:B1 to the bottom of the list in Columns A & B of the second sheet. It will then clear (erase) the conte...

How can I refresh data in a calculated field on my main form after sub form data changes?
Hi, I have created a database to store my bank transactions. On my main form which is the default form to open when the database starts, I have 2 subforms. One subform is for deposits and one is for withdrawals. On the main form I have a calculated field that shows my current balance. The problem is that after I have entered either a deposit or withdrawal the new balance does not refresh. The other problem is that I don't want to requery the main form because the query behind it will always ask me for my password so that it shows the information for the account I want to see, but this see...

Extending a Chart Data Series from an Array
I am trying to create some VBA code that will extend a chart data series from an Array. Google searches on the subject tell me that there is a known bug that will not allow this. The code fails on the last ActiveChart.SeriesCollection line. Any ideas for a workaround? Can I read the array into a range somehow and then use Range with the Extend method? Can I do this without writing the range somewhere? Here is the basic code .... 'Read all the datapoints into an array TotalRows = Range("MyChartSeries").Count Dim MyArray As Variant ReDim MyArray(1 To TotalRo...

How to sort data from one worksheet into other worksheets?
Hi! I am making an Excel workbook in which we can keep and assign tasks to various staff. I have one main worksheet, in which we will be assigning the tasks (using a drop-down list in the C-Column). I want to create other worksheets for each staff that shows only the task(s) row(s) from the main worksheet that is assigned to that person. Does anyone know of an easy way to do this? I take it that Column C has the staff list (in each drop-down). One way would be to sort the table by Column C. This would put all the tasks for each staff member in a group. Copy/paste from the...

How do I import data from a text file into Excel 2003
My data exceeds the number of lines in excel. Is there a way to import all of the data into multiple worksheets? -- mad In Excel 2003? Not really... You can either break your files into smaller pieces or write code to import it into multiple sheets. Excel 2007 allows 1,048,576 rows. See http://office.microsoft.com/en-us/excel/HP100738491033.aspx "madlin" wrote: > My data exceeds the number of lines in excel. Is there a way to import all > of the data into multiple worksheets? > -- > mad Run a macro to split as you import. http://suppor...

Data and Other Records
Hi - I'm building a pivot table consisting of counts of a list of items. I want to include only the first 50 items in detail, but lump all the others into an "other" category with a count on it, instead of detail on records above 50. Hopefully this makes some sense! Thanks, Greg. -- groggrog ------------------------------------------------------------------------ groggrog's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27400 View this thread: http://www.excelforum.com/showthread.php?threadid=469155 ...

Extract web-data
I would like to make a small application that displays key information relevant to a specific project. It could be - an exchange rate - oil-prices - weather data for a specific location - other data All the data is available on various web-sites - but I would like my program automatically to extract these values! Any idea on how to do that? I imagine to do it in Access as I would log the values in a table - but if other tools are deemed better for extracting web-values - please let me know. -- Diane It can be done in access - but much better (for the data you need)...

Can I sort text data by reference to text data in another column?
I have imported text data (e-mail addresses) into a worksheet that also has a column of text data (also e-mail addresses), many of which, but not all, are the same as the imported text data. I wish to sort the imported text data into the rows of the matching original text data. How do I do that? The basic process would be to get all email addresses into a single column. Then select ALL columns to be sorted and use Data --> Sort and choose the column with the email addresses as the column to base the sort on. Duplicate email addresses will end up on adjacent rows. &q...

Data Files in Exchange 2000
Hi! We have Exchange Server 2000 with SBS 2000. Recently I have noticed that the data files for my exchange server which resides in MDBDATA Folder (.edb amd .stm files) are not being updated with current date. Date modified fot this files is showing 1/27/2006. Eventhough our emails works fine without any trouble I am concerned about this data files are not being updated. Can anybody tell me if this is normal or not. Thanks Raj raj@debthelper.com normal...that date will not typically update until a restart of the store... -- Susan Conkey [MVP] "Raj Bhavsar" <raj@debthe...

Sorting Data & Updating Automatically
I have a list of names with corresponding numbers (sales data) and I a wondering if there is a way that when I change the numbers, the lis sorts automatically and updates itself and ranks from highest t lowest. I know how to do it manually but is there a way to sor automatically? TIA -- sabunab ----------------------------------------------------------------------- sabunabu's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2939 View this thread: http://www.excelforum.com/showthread.php?threadid=49792 So is this not possible -- sabunab -----------------------...

How can I eliminate rows that contain the same data as the row ab.
How can I compare rows in Excel aand eliminate those rows that have duplicate information in them? Use data>filter>advanced filter, select unique records only -- Regards, Peo Sjoblom "mjlucas49" <mjlucas49@discussions.microsoft.com> wrote in message news:56E5DD0C-E9D0-4C25-8956-C96F285D320B@microsoft.com... > How can I compare rows in Excel aand eliminate those rows that have duplicate > information in them? ...

Dynamic Days Of Month
I am building some automated reports based on the values in a linked DBase Table. The DBase columns are labeled "ONE" thru "THIRTYONE" for each day of the month. The values returned for each day are either 0, 100 or null. I am trying to differentiate between a null because the day is in the future and a null because there was no value entered that day. I thought if there was some way to relate each of the DBase columns to a separate table that had a true date value for each day of the month I could build an expression to return two different strings for the null b...

Merging Data #2
How may I merge my data onto a business card format stock, so that I will be able to place individual information on each card. This stock has ten cards to a page and I need to run approx 2500 names. Thank You ! Ken I'm not sure of what you want since I'm not familiar with "business card format stock", but it sounds like a label stock and you want to populate each label. Is that right? This can be done with VBA using a looping code to run through all 2500 names. If you wish, send me a sample of what you are working with and I'll try to help. My email ad...

Converting horizontal data to vertical data.
I have a worksheet with the following data. account name date checkno payment date checkno payment etc 100 fred 1/1/03 1234 35.45 2/1/03 3445 234.30 The spread sheet columns go to A to IU. What I need is each date,checkno ,payment group to be a new record for that account. The data is going to be populated into a SQL Server db. I have played around with doing a reverse pivot table, but I can't get the results I need. Any ideas on the fastest way to do this, would be appreciated. Thanks -- Dwight Trumbower T Systems Corp www.tsystemscorp.com Dwight, Select the entire table, an...

cannot enter data (normally)
For some reason I am not able to just enter plain data into spreadsheet by just typing. After I enter data into a cell and use tab to move over I have to user mouse to double click on the cell in order to enter! Same if I use the down arrow to enter data into cell one row down. I don't understand this, never had the problem before on my old laptop. Excel 2000, Window XP Pro, Media Center Edition. Check your scroll lock, button usually at the top right of the keyboard near print screen, if the page moves instead of the cell changing, that is most likely your problem -- -John Nor...

Compare worksheet data
I have two worksheets that are in separate workbooks that I need to compare. The information is structured the same in both sheets. However, there was an update to the bill of material which may have added rows of information throughout the sheet, or may have added additional information within the columns of other components. However, both worksheets have the exact same headings and formatting. Is there any way I can compare the two and update the older worksheet with updates from the new? Can someone help please? Thank you Candida ...

Generating Data based on a List
Hi all, Just wondering if someone can help me. I have Sheet A with Col A with Town Names... and Col B with the postcode. there is about 150 towns. I then have another sheet... Sheet B... which has about 20 rows of information...some of which grabs information the contents of A1 and B1 from Sheet B.... into functions. That works fine. However I dont want to have to enter the 150 A1 and B1 items manuelly... and then have to save them as text files manuelly each time.... Basically If i could have it somehow read the values from Sheet A and put them into Sheet B... one by one.. it would then ...

data convertion
Is there a way to import comma separates data into money? Convert it to QIF, then import the QIF. See the answer to the question "How do I import from Excel (or .CSV or etc.)?" in the FAQ available at http://www.bollar.org/msmoney. "L Silva" <lmsilvaa@aol.com> wrote in message news:8f4e01c35b6e$f95ae640$a001280a@phx.gbl... > Is there a way to import comma separates data into money? ...

How do you import data from a lotus file with .OR6 ext?
I have been using Lotus Organizer for years and am switching to Outlook and want to import my calendar.OR6 file. My version of outlook only allows importing of .OR5 file. Is there a way I can update my outlook version. I called Microsoft tech support and was charged $35 and never received an answer or a reply after several emails. donalbert wrote: > I have been using Lotus Organizer for years and am switching to > Outlook and want to import my calendar.OR6 file. My version of > outlook only allows importing of .OR5 file. Is there a way I can > update my outlook version. ...

move cursor after inputting data without using enter key
I am using a scanner that reads barcodes and automatically enters data into a cell. The problem is that I'm not always close to the computer and want to have the cursor automatically go to the next cell after i scan one code without having to press enter after every scan. Is there a module or marco that can do this. This is not something that Excel could do. Even an event-driven macro couldn't be triggered until cell editting is completed (when ENTER is hit). However, you should be able to configure your barcode reader to automatically append an ENTER (or Carriage Return) to the ...

select this weeks data only
I have a worksheet thats created from a data import from an accounts package I'd like to sum the values of sales for this week only starting Monday and the SUM result would grow to Friday before returning to zero next monday morning the worksheet has multiple entries for each day, in date format dd/mm/yyyy at the moment. I'm managing to count number of sales for the month per sales rep, with the formula below but seem to struggle for current week only reporting, (plus I need SUM of Sales as well as the COUNT) =COUNT(IF((data!FU$2:FU$6474="salesrep1")*(MONTH(data!M$2:M$64...

How do I create a combination chart and table with different data.
I am using Windows XP, and Microsoft Excel 2003. I have been asked to create scorecards with charts displaying rates graphically, and an attached table underneath with numerator and denominator data. The combination graphs in the custom charts try to graph and display all the data. Can I create a combination chart with the rates graphically displayed and the underlying numerator/denominator data presented in a linked table underneath the chart? Or do I need to create the graph and table separately? You would do better to make a separate chart and table, because chart data tables ...