The arguments for the GETPIVOTDATA function need to reference a cell/column in the table, and it's only PART of the argument that needs to be replaced. The GETPIVOTDATA function uses the following arguments: Data_field (required argument) – This is the worksheet information from which we intend to remove nonprintable characters. I'm trying to use GetPivotData to query "Foo Bar" values from a pivot table. The following figure shows the complete work we have done so far. Now, you can turn off GETPIVOTDATA and use cell references. Basically Im trying to retrieve data from multiple data fields. Remove the filter. Worksheet: CNM (A4:E10) 2. That's exactly what, I was looking for! Using the GETPIVOTDATA function helps ensure that the formula will continue to reference the pointed cells if the pivot table layout is changed. Thank you. I need to use a cell reference in that GetPivotData function. In the example, shown above data filed i.e. In the example below the 18418 needs to be replaced with the data in the same row of the table in a column named ID. Just by typing = in a cell and clicking on the pivot table does most of the legwork, as Excel writes most of the formula. typed in i want to reference this part of the field item to a cell which contains the word "April" so that when i simply change the month in that cell (lets say for argument sake its in A3) all the formula's will change to say "May" and so i dont have to change each formula. For example, if you are constructing a simple SUM calculation that references a total in a PivotTable cell, you may expect to see a cell reference like A2. I have a Table in which I need to use a GetPivotData function. Sales Amt is residing in the cell A3 that is why it is taking a reference as A3. GETPIVOTDATA Function in Excel is very simple and easy to use. /Sune Thanks. GETPIVOTDATA to the rescue! Item1). [Field1, Item1]: We are looking for a Grand Total of Sales Amt for the Region (i.e. How the GETPIVOTDATA function works. Note: cell B14 now references the amount of carrots exported to France, not the amount of beans. I have done this before with no problem in a normal cell range, but it seems like the syntax when using a Table screws things up. "Max" wrote: > The T function seems to work ok when I tested it lightly, Obviously!! The syntax for the GETPIVOTDATA function is as follows: GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], …) Select cell B14 again, type an equal sign (=) and click cell … But it seems I am wrong – you can use a cell reference to indicate which data field you want to return, you just need to concatenate with an empty string “”. For example I get: =GETPIVOTDATA("Foo Bar", 'Pivot Table'!D1, "Day", DAY(2010,10,10)) → 42 I need to have the first and last argument referenced to other cells and some extra dollars. Note that I inserted the first break immediately after the n of allen and before the space, just before and after 1944.... Then we can replace "Allen" with a cell reference, and "1944" with a cell reference, since those cells contain strings that would be inserted in place of the cell reference when Excel evaluates the string formula. How to Use GETPIVOTDATA Function in Excel? [Field1]) East (i.e. Hi there, Im stuck with Getpivotdata. If you type the cell references manually (instead of pointing to them), Excel will not use the GETPIVOTDATA function. 3. Instead, when you click on the cell, you get a very long GETPIVOTDATA function added to the formula. Use the filter to only show the amounts of vegetables exported to each country. I have a pivot table on one worksheet and in another I'm trying to use the getpivotdata function to pull out data based on cell references. Pivot_table (required argument) – This is a reference to a cell, range of cells, or named range of cells in a pivot table.