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. T

    Filtering rows in pivot chart

    Fairly new to excel. I have created a pivot table and organised it to display what I need. When creating a pivot chart, when I create a slicer I cannot filter the data/rows I want to view. It may be that I need to add something to the raw data or that I need to change my pivot table/chart...
  3. D

    Why can't I change the "Summarize By" (sum, count, average) section of a calculated field in a Pivot Table?

    I'm learning to use PivotTable and I'm attempting to create calculated fields and change them to counts or sums, but it's greyed out? No matter which data source I use, how simple the calculation is etc. I wanted to change Sum to Average, so to get average profit and average cost per age group...
  4. Jyggalag

    Implement IFERROR formula for data in Pivot Table?

    Hi all, I have this data sheet: I have turned it into percentages and I assume that the top value (0.36% & -1.52%) are the sums of my columns (I am very new to Pivot, sorry). But I have #DIV/0! errors! :( Is there a way in which I can implement an IFERROR formula for my entire pivot table...
  5. Jyggalag

    Pivot Table with Query

    Hi All, I am currently facing an issue in with my pivot table that I have been absolutely unable to fix and it does not seem like the Power Pivot forum is very active compared to this one. In addition, mine is more related to Pivot Tables than Power BI, Query or anything like that, so I may...
  6. Jyggalag

    PowerPivot / Power Query & Pivot Table??

    Dear all, I have currently made a pivot table, which I am linking to some Power Query data. The data in the power query comes from a folder where I import files from a website, so these files will ALWAYS have the same file name, sheet name and column names. However, the data under their...
  7. G

    VBA code to copy a cell value in a pivot table from one sheet to another sheet

    I would like to know how to copy a variable cell value in a pivot table from one sheet to another. I would like to copy Sum X Est from label Buy and S. The code must work even if there is a row missing of data. I tried GetPivotData("labels", C1, "Buy", R2, "S", R3, "Sum X Est.", C2) but it...
  8. D

    VBA to stop Pivot Table refreshing if blank date will ungroup groupings

    Morning All, I'm trying to avoid my dozens of pivot tables date groupings being lost in the event of a date being accidentally left blank in the source data. I've been toying around with the PivotTableBeforeAllocateChanges event but have had no joy, it just doesn't seem to work. As a test I...
  9. M

    Using a PivotTable to create a simplified table

    I'm trying to covert a table of data into a more usable format - currently the data is laid out like this, with the hours for each resource broken down by task as shown below: Task Name Resource 1 Resource 2 Resource 3 Resource 4 Resource 5 Resource 6 Task #1 6 10 3 Task #2 8...
  10. H

    Powerpivot: "the relationship cannot be created because each column contains duplicate values. Select at least one column that contains only unique"

    Hi, I have a problem getting two tables to work together despite unique relationship. The table 3 has a column (date) which is unique. The Table 1 and 2 have also dates but not unique. When building the relations between these it works fine, but when i excel trying to use these data in a pivot...
  11. wsnyder

    Run Time Error 1004 'Unable to get the PivotFields property of the PivotTable class

    Hi all, Using Excel365 My code is retuning an error Not sure why? I am trying to add 4 Row Fields to the Pivot Table. The first 3 are added to the Pivot, but not the 4th. I see the column in the data source I can manually drag the field from the Field List to the Rows Fields Not adding the...
  12. 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...
  13. wsnyder

    Excel Data Model Pivot Table Grand Total

    Hi all, Using Excel 365. I highlighted my data >> Insert Pivot Table >> Add this Data to Data Model. I added fields to Page Filter, Filtered for a vendor I added document # to Rows and Distinct Count of Type to Values There are 44 Document #'s and the Distinct Count of Type for each Document...
  14. D

    Filter Pivot Row based on Multiple Measures

    Hi, I am hoping one of you fine people can assist, I am using VBA with a data model to draw Pivot Tables (the data is loaded to PowerQuery Only and saved as a connection). Drawing the Pivots was fine once i got my head around the nuances of CubeFields with the help of trusty WiseOwl videos...
  15. A

    Pivot Table Data Model referencing wrong range

    I have created some code that created a data model based pivot table that just displays a type, qty and concatenated string built from a column of references. This all works fine on the first run. Where it goes wrong is if I run it again on the same worksheet, even defining a different range, it...
  16. P

    pivot table from two datasources

    Hello Excel-enthusiast, I'm trying to use data, from two tables, in a pivot table. I made a relationship between de referece columns of table 1 & 2, but when I try to add the Weight column to the pivot table it sums the weights for every row? I just want to show the weight for every reference...
  17. G

    Run-time error 5: Invalid procedure call or argument in PivotTable filter VBA

    I'm trying to copy, create and rename a sheet named "ASPAC CAP" and update the selection pivot table field of the newly created and renamed sheet. The macro below successfully copied, created and renamed the new sheet. however I'm encountering a code error below when filtering the pivot table...
  18. S

    PivotTable Macro Filter

    Hi, I would like to be able to filter my pivot table by typing into cell D1 e.g. 111111 This Sub is from me just recording a macro of me manually filtering. Sub Macro2() ' ' Macro2 Macro ' ActiveSheet.PivotTables("PivotTable1").PivotFields( _ "[Item].[Item Code].[Item...
  19. C

    Pivot Table repeating data

    I created a report that pulls raw data from QuickBooks that shows the payments we've received from customers over a certain time frame and gives the detail on the payment itself as well as the invoice the payment was applied to. When we receive separate payments on different days that are...
  20. E

    Importing Data into a PivotTable Report from Access in Office 365

    I am trying to import data from an Access database into a PivotTable Report in Excel. The instructions I have are not matching up with what is happening in the Excel program I am using. I have Excel 365. I am able to get to locate where to import "Get" the data from Access the issue is how do...
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