getpivotdata

  1. L

    Getpivotdata formula error retuning #ref

    Hi I work for a preschool in the UK and have developed my own spreadsheets and learnt how to insert pivot tables to run a number of reports. I am stumped though. I run a daily breakdown of children and staff so that we can ensure that we are staffed in the correct ratio of adults to...
  2. 8

    Attempting to use GETPIVOTDATA to find information for an employee based on referenced date.

    Hello, I have only recently started diving into GETPIVOTDATA and I am stumped as to how to have the following happen. I would like to have the information for an employee to show up in a cell on another sheet by referencing their name on that sheet. but because we need to deal with different...
  3. S

    How to reference a range of cells containing monthnameshort in GETPIVOTDATA

    I am trying to build a more agile and time limiting financial reporting tool. In this proces I stumpled upon a problem that I can't seem to fix. I am trying to get Year-to-date numbers by referencing a range of cells containing Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep Formula that works for january...
  4. D

    Average formula with GetPivotData

    Hi All, I have been looking over some GetPivotData and Average solutions and can’t seem to find a way to correctly do what I need. I am attempting to create an Average formula that has a dynamic range using GetPivotData option. Is it possible for GetPivotData to return a range or a range of...
  5. S

    How to get Multiple items and sum up the same using GetPivotData?

    Sub Reconcile() Dim InputworkBook As Workbook Dim ReportsToReconcile As Variant ReportsToReconcile = Array("Report1", "Report2") Dim ReconcilingReport As Variant Dim ReportIndex As Integer ReportIndex = 0 Set InputworkBook = Application.Workbooks.Open(Application.GetOpenFilename, ReadOnly:=True)...
  6. S

    How to GetPivotData passively?

    This works... .GetPivotData(PivotEntity) But Not this... Evaluate(".GetPivotData(" & PivotEntity & ")") How to make this work? If not... Is there any other alternative command available. Please Help. Thank You
  7. T

    Vlookup/Get Pivot/Index Match without converting reference cell to text

    I am trying to find a method for pulling a value from a pivot table using a reference cell. My first thought was to use GETPIVOTDATA, but when I use an "=" and then click on the cell I want it comes back: =GETPIVOTDATA("Actual FTE's",$A$17,"SSC|DEPT...
  8. Z

    Power GETPIVOTDATA - based on multiple items in CELL reference

    HI, I am trying to create formula that will extract data from power pivot table based on cell reference. Issue is that cell contains more then one Item, separated with " ; " , I can change this in any form, but I need to keep it in one cell Example Account num. A2 = 5110;51100 Is it possible...
  9. R

    GETPIVOTDATA formula to return highest rank

    I have a pivot table with a parent and children rows. I need a formula to return the highest, 2nd highest, 3rd - 10th highest ranks from from columns A-C Pivot Example: Business 1 Transaction Count % Approval Rate Transaction Count Ranking Bank A 25% 80% 1 Bank B 22% 85% 2...
  10. J

    I need to replace 0's with NA

    Good morning, I am looking to update a dynamic getpivotdata table so the results can be charted in a bar chart (a pivot table is used to summarise the getpivotdata table so only relevant cells are reflected in the chart). The issue is that the getpivotdata table includes REF errors that impact...
  11. R

    How do sum up multiple items for the same field using cell references in GETPIVOTDATA

    Dear Excel Experts I want to sum up data for more than 2 fields from a pivot table using GETPIVOTDATA formula. When I linked it with one field it is giving correct result as show below =SUM(IFERROR((GETPIVOTDATA("Sum of...
  12. B

    Sum If a Pivot Table Data Set Based on Date

    Hi Gurus, Am I able to sumif a range of dates within a pivot table. The formula I have is as below, but returns a #ref . =GETPIVOTDATA("DUE",$A$3,"Group","AP","Payment Date","<"&DATE(2019, 6, 21)) <tbody> Sum of DUE Column Labels Week Ending AP CX 1/03/2019 10/05/2019...
  13. M

    Dynamic criteria for SUMIF or dynamic getpivotdata

    Hi Everyone, I need help with a spreasheet issue related to my trading strategy. It would be great if I could upload the excel file, but it is too large. So I will carefully explain the problem with several images labelled in a word document -...
  14. C

    Extracting data from Pivot Table

    Hi all I'm trying to automate a monthly report and all the information I need is presented within a pivot table. The pivot table contains two columns, A is Customer, B is value. The pivot table has an additional row within the customer to subdivide them into categories. Essentially i'd like to...
  15. D

    Can I use a condition in GETPIVOTDATA?

    I have a pivot table that is tracking occurrence per device as follows: <colgroup><col><col></colgroup><tbody> PJ1065 1 PJ1089 1 PJ1156 1 PJ1197 1 PJ1573 2 PJ1754 1 PJ3159 1 PJ3223 3 PJ3648 1 PJ3944 1 PJ4064 1 PJ4073 1 PJ4309 1 </tbody> I need to find a way to...
  16. P

    Getpivotdata

    Hello All, GetPivotdata formula will give column values from a Pivot. Is there any formula similar to it which will give row label data from a Pivot?
  17. A

    GETPIVOTDATA conditional grand total

    Dear Excel Community, Currently I have a tab that references data from a pivot table via getpivotdata function. How can I modify the getpivotdata formula in a way that it will give me the grand total value up to a certain month. For example, I would like the grand total from the average price...
  18. A

    Getpivotdata

    Dear Excel Community, Currently I have a tab that references data from a pivot table via getpivotdata function. How can I modify the getpivotdata formula in a way that it will give me the grand total value up to a certain month. For example, I would like the grand total from the average price...
  19. R

    GetPivotData Data Model with Date as Criteria

    Hi - using PP Data model pivot is really new for me, i am trying to use getpivotdata below is the formula =GETPIVOTDATA("[Measures].[Distinct Count of nama]",$K$2,"[Range].[date]","[Range].[date].&[2019-03-12T00:00:00]") i want to get the data based on the date. i tried looking over the...
  20. B

    using getpivotdata as reference point to sum range

    I have a pivot table where my rows are client names an the columns are dates. Is there a way that I can use the getpivotdata function to identify a reference starting point and then sum the prior twelve dates/columns. If there aren't 12 prior dates/column, it would just return zero? I am...
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top