Unable To Drag PivotTable Fields

Aug 27, 2007

The issue is that for every pivot table in any file running on my machine, I am unable to drag the column or row values to change the sort order - I.e. if the row is Jan/Feb/Mar/etc., I can't drag Jan down to the middle or rearrange them - it will sort by ascending or descending value just fine, but simply won't allow me to rearrange - the 4-way arrow doesn't appear where it should when mousing over the fields. I can drag fields on/off and from/to the column/row/data sum areas, but I simply cannot re-arrange the order of the values (and yes, the Autosort checkbox is set to "manual" in field settings.)

ADVERTISEMENT

Unhide All PivotTable Fields

Jan 11, 2008

In a pivot table the user is able to select multiple entities to hide in the 'Hide Items' field of the PivotTable Fields window. I would like to create a macro (or edit a macro in the VB Editor) that willl reset all 'Hide Items' in the PivotTable Fields window...effectively unselecting any previously hidden entities.

View 2 Replies View Related

Link PivotTable Page Fields

Nov 20, 2006

I have a workbook with three sheets. The first is a list of employees with their managers. The second is the data about those employees (who they sold product to, and how much, on what date). The third is the PivotTable with the employees' sales. I have the Customer name in the Row field, and the amount in the Data field. I have two page fields - the Manager's name and the Employee's name. The example that I'm using so as not to violate my NDA is a Northwind knockoff. That's why the names probably sound familiar.

What I want to do is have the second pivottable page field (Employee) change, so that when I choose a manager's name, only the employees who report to that manager show up in that page field. I have a named range called "allEmp" (all employees), one called "buchanan" (all the employees who report to Steven Buchanan), one called "fuller" (all the employees who report to Andrew Fuller). (File attached, btw).

Private Sub Worksheet_Calculate()
Dim mgrRange As Range
mgrRange = Range("allEmp")
Range("$b$1").Select
Select Case ActiveCell.Value
Case "Andrew Fuller"
ActiveWorkbook.PivotTable("EmpSales").PivotFields("Employee").CurrentPage = Range("fuller")
Case "Steven Buchanan"
ActiveWorkbook.PivotTable("EmpSales").PivotFields("Employee").CurrentPage = Range("buchanan")
Case "Susan Erickson"................

View 6 Replies View Related

Group & Hide PivotTable Details Of Certain Fields

Oct 8, 2008

I have a pivot which under a Main product, there are a few sub products. I have act, budget, forecast etc figures but mainly want to only show the Total Act and not the rest, see the highlighted brown columns which I do not want to show in the pivot.

Then I want to group the clients, salesperson together so that the details can be hidden.

This is a small example. I have a large pivot table with lots of teams and salespeople and clients and a few main products with a few sub products within it. Grouping it manually is going to be very difficult and would appreciate if there is some way to automate it either via pivot capability or VBA.

View 4 Replies View Related

Pivot Tables & Formula: Build A Pivottable And Let Users Move The Fields Around

Apr 28, 2007

I'm trying to do at the moment is build a user interface for some Excel Data. Now, the natural way to do this would be to build a pivottable and let users move the fields around, and this is how the thing has worked in previous iterations of the workbook. It seems now, hoever, that we're trying to cater for users for whom pivot tables are just a little too much and to summaries data based on listboxes etc.

What I'm interested in knowing is whether there are ways of interacting with PivotTables beyond the GETPIVOTDATA function - is there any way, for example, to select different field items to show based upon an input from elsewhere? How does one tell a pivottable what to do from outside the pivottable?

View 3 Replies View Related

Excel Pie Chart - Unable To Use Sums For Fields / Axis?

Aug 4, 2014

I have a set of data for hours of work. I want to make a simple Pie Chart: % of Hours worked overtime, and % of work not overtime.

The Data Looks something like this:

Name,Normal Hours Worked, Overtime Worked
David,8.0,3.2
Anne,8.0,1.3
Danny,7.0,0.0
Harry,8.0,3.0
Edward,8.0,2.3

So I want a pie chart that has 2 wedges.
~25% labeled as overtime worked
~75% labeled as normal hours worked

Whenever I put these values in the pivot table and use SUM, it is trying to sum based on # of hours matching, instead of by the column Name... so it gets divided into a bunch of different pie wedges.

View 2 Replies View Related

PivotTable Code: The PivotTable Field Name Is Not Valid

Aug 3, 2007

The code I'm using that results in the following error message: "Run-Time Error '1004':

The pivotTable field name is not valid. To create a pivottable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a pivottable field, you must type a new name for the field." Here's the code, I've seperated it into each sub hoping that will make it easier to read:

Option Explicit

Sub main_prog()
Call td_metrics_import
Call pt_td_metrics("Pivot_Page1", "PivotTable1", "PivotTable2")
Call pt_td_metrics("Pivot_Page2", "PivotTable3", "PivotTable4")
Call create_graph
End Sub...............

View 8 Replies View Related

Convert Column Data Fields To Row Data Fields In Pivot Table

Feb 8, 2014

CountryHourDataTotalData
Austria - A10Sum of SeiA51CountryHourSum of SeiASum of SeiT
Sum of SeiT4.88Austria - A10514.88
1Sum of SeiA561562.83

[Code]....

left side pivot created in vb 6.0 & right side pivot table created manually in excel.

i want to generated pivot table using vb 6.0 same as right side pivot.

Set PRange = ws1.Range("R1:Y" & finalrow)
Set PTCache = wb.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)
Set PT = PTCache.CreatePivotTable(TableDestination:=ws2.Cells(1, 1),

[Code]....

View 2 Replies View Related

Creating A PivotTable Using VBA

Apr 30, 2014

I am unsure if this is supposed to be in the PivotTable or VB section, but given it is VB code I expect here is better.

[Code] ......

I can confirm the SourceData exists, and can be used to create a pivot table without error. Only when I try to RUN this code does it kick out saying, "Invalid procedure call or argument".

View 3 Replies View Related

Name New Sheet Used For PivotTable

Oct 8, 2007

In the macro I am writing I open a pivot table which as you know creates a sheet. I would like to name that sheet. My problem is that the default name(number if you will) changes every time I run my macro so I am unable to define which sheet I want to give a name.

View 2 Replies View Related

Possible For Count & Sum In PivotTable

May 20, 2008

Is it possible to have both SUM and COUNT values on one PivotTable?

View 3 Replies View Related

Hide PivotTable Row Labels?

Feb 4, 2014

I have a PivotTable with data and I want to hide the row labels (in blue) that's in the first row.

See image:[URL]

PivotTable.jpg

View 4 Replies View Related

Values Appear Twice In PivotTable Row Labels

Jan 31, 2010

I have a PivotTable that is summarizing a data table. In the configuration panel, I've dragged one column ("C92") to the "Row Labels" area. Likewise, I've dragged the same column to the "Values" area, where it defaults to "Count of C93". So far, so good.

I should mention that my values in column C92 are the whole numbers 0-7 and the string "NA". But in the output chart, each of these values is listed twice! And each count of each value is different! I'm attaching a cut-down version of the spreadsheet that shows this behavior.

View 2 Replies View Related

Automate Autofill For PivotTable

Dec 22, 2009

I have a document that is exported from an SQL database. Once I have the data I need to throw it into a PivotTable to analyze it. However, the export doesn't fill every cell with the information - instead displaying the cell value once until a new value comes up. Therein, there are often a number of blank cells underneath each entry.

I can overcome this by going into the document and autofilling each cell that doesn't have the information in it. However, some of the reports are very large and it is quite time consuming.

What I'm after is for a bit of VBA that will autofill the cells until it hits another new cell value then autofill this down until it hits another new cell value, etc.

I have attached a dummy copy of the report - one sheet, Raw Data, shows the data as it is exported and the other, Autofilled, shows how I would like the data to be displayed after the code is run.

View 2 Replies View Related

VBA: Create PivotTable From A DAO Recordset

Sep 24, 2009

I have been creating pivot tables (from the active workbook) with ADO for a while and its working great.

I just tried to switch to DAO and adapted my code accordingly but the new code fail at:

Code: ....

View 9 Replies View Related

Adding Columns Within A Pivottable

Jun 19, 2007

I have a data worksheet, which contains about 20,000 lines of journal entry info. There is a separate column for debit and credit, not one "amount" column. On another worksheet, I am aggregating the data to review the impact for this series of journal entries over a time period (12 months). Thus, my pivottable contains "Accounts" in the "ROW" and "Period" in the "COLUMN" sections of the PivotTable Layout. The "Debit" and "Credit" fields are then summed in the "DATA" portion of the PivotTable.

My issue revolves around getting the actual impact for each account for each time period. Essentially, I need an extra column inserted between each time period that sums the debits and credits for that period. Do I need a macro for this (not any experience with macros)? My file is way to big to share, so let me know if a pared-down sample would work.

View 5 Replies View Related

Hide PivotTable Columns

Jul 17, 2007

Hide values in Column layout on pivot table i only want to show th total value as its based on quarter figures not per month and this way it looks like Quarterly totals are broken down per month and has to have 200 a mont whenits in fact 200 for the complete quarter, how can i had the 200 for each of the month and just have the total per country? This is what my pivot table looks like

county Month Quarterly totals

UK June 200
july 200
August 200

Uk Total 200

View 3 Replies View Related

Preserve Formatting In PivotTable

Sep 18, 2007

I have made a pivot table and in Pivot table option Merge labels for colum b. I the problem i am facing is once i give this the next colum text filed is automatically arranged in center. I want the next colum as left alignment. Once i delete some rows in the data then if i refresh it automatically align the next colum to center. I have given the preserve formatting as well as i have un selected the autoformat colum.

View 2 Replies View Related

Refresh All PivotTable In Workbook

Jan 10, 2008

I am trying to run a macro inside a Worksheet_Change event.

Private Sub Worksheet_Change(ByVal Target As Excel. Range)
If Not Application.Intersect(Target, Range("Mon_Data")) Is Nothing Then
If Target.Value > 0 Then
Sub Refresh_PivotTables()
'
' Refresh_PivotTables Macro
' Macro recorded 10/1/2008 by JackChappers
'

as you can see, i put the macro within the worksheet_Change event so that, when data is changed on another sheet (target.Value >0), the Macro is run (the macro, by the way, refreshes some Pivot Tables).

The refresh Refresh_PivotTables Macro works on it's own, but when i use it like this, i get the message:

"Compile error:

Ambiguous name detected: Worksheet_Change"

If it makes any difference, i also have another Worksheet_Change event above performing another action. I think that may be the problem, if it is, how do i run them both without the error?

View 4 Replies View Related

Count Of Associated Items In PivotTable

May 8, 2008

I need to make a pivot table with data from 2 columns. The data has columns named Unit Type and Name. There are 9 different unit types and over a hundred different names. I need to find a way to make a table that counts however many times the name VACANT shows up for each unit type. Is this possible using a pivot table?

View 3 Replies View Related

Add Calculation To Date Field In PivotTable?

Jun 18, 2014

I have a pivot table with a field called "created on" which is a series of dates, i want to create a field called "Days in Pipeline" which would essentially be =TODAY()-'Created On'

This doesn't seem to work, i get an error: Your formula includes a function that cannot be used in PivotTable formulas...

View 1 Replies View Related

Select And Print Used Pivottable Columns?

May 21, 2014

I would like to somehow select and print the used columns in my pivot table.

I currently have a code that prints TableRange2 but this also includes blank pivot tables columns which are not in use.

View 2 Replies View Related

PivotTable - Adding Page Filters

Aug 11, 2014

First, the code:

[Code] ....

My problem is with this part here:

[Code] .....

I need it to filter on anything beginning with 2613, not just 261302. Unfortunately, I do not know all the possible inclusions or I could just add them individually, so I need a wildcard to add to this filter and I have never done that.

Thus, I ask how to write a wildcard filter that will work in this pivottable.

View 1 Replies View Related

PivotTable Error: Field Name Is Not Valid.

Jan 27, 2009

I successfully created two PivotTables two days ago, but when I added more rows of data to the source worksheet I could not refresh either PivotTable view. So after much frustration, I deleted both worksheets and again tried to create a new PivotTable using the wizard. I keep getting this error, and have no idea what it is telling me so that I can go about fixing it:

"The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field."

View 2 Replies View Related

Error Extracting Pivottable Data With VBA

Mar 29, 2007

I try to extract datas from a pivottable (so one data source) which could change.

The items of one field are changing (given by the user through Inputbox): "batch"

The items of the second field ("type plate") are at each time visible (the macro make them visible to have the complete information concerning the "batch")

So I want to extract values and labels from one particular pivottable (the one I display), selecting several batches. But the problem is that some of the items have no value, so disapear fron the table) even if they are visible. As I extract the value on each item (see code below) I get an error message saying "runtime error 1004 application-defined" when the macro read "valeur".

Dim pt1 As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim pj As PivotItem
Dim i As Double
Dim valeur As String

I tried to go through this problem using "on error goto solution" but it works only one time, and after same error message

How coud I avoid to calculate "valeur" with the Items (pi) not displayed ?

View 9 Replies View Related

Show All Items Of PivotTable Field

Mar 29, 2008

I have a section of code that takes SO long, but I have to have it. Bascially, I need a pivot field to be set to "All", but there HAS to be a faster way. Here's my

Sub FloorCompareSetter()
Dim pt As PivotTable
Dim pi As PivotItem
Set pt = ActiveSheet.PivotTables("PinPointPivot")
' Speeds up code dramatically
pt.ManualUpdate = True
'Set the floor comparison for managers, coaches, and reps
' Make sure all PivotItems along line are visible
For Each pi In _
pt.PivotFields("Manager").PivotItems
pi.Visible = True
Next pi
pt.ManualUpdate = False
End Sub

Auto Merged Post Until 24 Hrs Passes;Also, note that I have used other techniques to speed up the process:

With Application
. ScreenUpdating = False
.EnableEvents = False
.Calculation = xlManual
End With

View 8 Replies View Related

Sum Total Of Calculated Field In PivotTable

Apr 28, 2008

i have a database of monthly sales of a regional distributor and im using a pivot table to manipulate my data. i want to show only what account makes a positive sales for a specific product and i want to get the total number of accounts who have the positive sales. there are multiple entries in my database, the condition is if an account have positive sales of a specific product regardless on how many times the account bought the result should be 1 (meaning one buying account)

i've tried to use a calculated field in my pivot table, i get the correct result per account but when it comes to grand total its not getting the sum of the total number of accounts. here is the sample of what i need to get..

you can see that there are multiple entries but it shows only 1 per account when it has at least 1 positive sales of a specific product and 0 if it hasn't any sales/negative... and for the total it adds the number of accounts to get the total number of buying accounts..

View 6 Replies View Related

‘The PivotTable Report Will Not Fit On The Sheet. Do You Want To Show As Much As Poss

Aug 28, 2009

Since I'm making a PivotTable with my macro, when it runs I always get this...

‘The PivotTable report will not fit on the sheet. Do you want to show as much as possible?’

I always choose yes, because in the end the user isn't going to use all of the data, they will be narrowing it down. Anyway, this comes up a few times througout the macro. Is there something I can put in the code that, if pop up box, always choose yes?

View 2 Replies View Related

OLEDB Data Connection And PivotTable Report

May 8, 2014

I am connecting Excel to a database and there is a field called Data Created which is timestamp. Data comes out nicely in a Pivot Table

1. I would like to be able to group by month but the grouping fiction is grayed out

2. How can we convert the timestamp to only date format. I am able to select only Date for that column but it seems that it keeps the time, even though it only shows the date in the pivot table the bar above shows date and time.

View 1 Replies View Related

Changing Source Data Of An Existing PivotTable

Jun 2, 2008

I'm trying to change the source data of an existing PivotTable so that it is linked to a raw data source in another workbook. The workbook it pulls the data from changes every month so I made this flexible code that is meant to pull the raw data every month:

Sub ImportNewSource()
Dim Filt As String
Dim FilterIndex As Integer
Dim Title As String
Dim FilePath As Variant
Dim ThisPivot As PivotTable
Dim FileName As String
Dim ShtNum As Integer
Dim LastRow As Long

The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field.

When I do it manually, it works just fine. There aren't any changes to the PivotTable field or anything like that so I'm stumped. I've tested the range that has the source data and its selecting the proper range and when I do it manually it works fine.

View 9 Replies View Related

Prevent PivotTable Refresh Showing New Data

Feb 18, 2009

I have about 10 pivot tables that only have one Pivot Field Item Selected (i.e., Jane Smith) with dates as column fields and a sum for data items. Everytime I refresh the pivot tables and there is a new person in the data tab (which is the data the pivot table is referenced to), the new name populates in the pivot table. I am sure I could include all of the names in one pivot table, but there is a long explanation why I only keep one name per pivot table in this particular workbook.

Attached is an example. As you can see, when you refresh the pivot table, the new name (Jason Smith) which I added to the data tab, will be included in the pivot table.

I am trying to figure out if there is way either with VBA or without VBA to prevent all of the pivot tables in the workbook to not select any new Pivot Field Items when I refresh the pivot tables.

View 9 Replies View Related

PivotTable Total Cell Field Reference

Aug 17, 2006

What code can I use to reference the total cell at the end of a field called "Calls Offered" in the data area of a pivot table called "PivotTable2"?

View 8 Replies View Related

Pivottable Total Include Hidden Items

Feb 21, 2007

I am using Excel 200o to create a PivotTable but am having problems wiht the Totals. Is it possible to create a PivotTable that includes hidden items in the Totals (row)? I tried the Subtotal Hidden Page Items option but this seemed to do nothing to the Total or subtotal.

View 3 Replies View Related

Multiple Condition Sum Across PivotTable & Another File Range

Oct 10, 2007

I need to find a value in a pivot table with a range of values over 12 months (Book5) when 3 criteria are met, Branch#, LOB# and Month#. Sum(if() works for the 1st month, but I do not know how to do the coding when the month changes.

Attached are copies of the two files I am working with and my coding so far....

View 5 Replies View Related

Breakdown Details Of Chosen PivotTable Item

Mar 13, 2008

Let's say I have pivot data in the form of:

| Major Category | Minor Category |
toys | balls |
toys | puzzles |
toys | blocks |
clothes | shirts |
clothes | pants |

I want to loop through every minor category and display it and its major_category on a different worksheet. (I'm doing more than this, but for simplicity sake ...

View 9 Replies View Related

Reformat Single Column Data For PivotTable

Jun 18, 2008

I have a worksheet of data collected from on online list of names and addresses all in one column. I'd like to pivot the data so I can sort it, etc. Unfortunately, the entries are not the same length (meaning some have five rows some have six or seven, etc. I have created an only file so everyone can see the issue.

a href=[url]

View 4 Replies View Related

PivotTable Calculated Field With IF Function/Formula

Jun 23, 2008

I have a PivotTable which shows the number of packs produced in a month. I created a calculated field which shows the number of vehicles needed to move these packs. The number of packs per vehicle is dependent on two criteria, the Pack Description and the Store. My calculated field, Shunts, is described as:

=Packs / IF(Store ="Middlewich",IF('Pack desc' ="Tall",30,60),IF('Pack desc' ="Tall",26,52))

which represents the logic

Middlewich & Tall = Packs / 30
Middlewich & other = Packs / 60
Other & tall = Packs / 26
Other & other = Packs / 52

However, it doesn't work!

I know Calculated Fields can be difficult to work with, but this is crazy and I think I'm missing something obvious as I'm sure I've had IFs work like this before!
I've attached my example, also showing the values I'd expect to see.

View 2 Replies View Related

Don't Overwrite Existing Cells On PivotTable Refresh

Jun 28, 2008

When I use a macro to refresh all pivot tables, I get a whole bunch of messages popping up, asking me if I want to overwrite the existing cells. Is there a way that I can say no to all of these messages automatically?

View 6 Replies View Related

Create PivotTable With Two Columns Of Data For The Same Column Header?

Dec 24, 2012

Can I create a PivotTable with two columns of data for the same Column header?

I have created a PivotTable in Excel 2003 with months for rows and cities for columns. I would like to have TWO columns of data for each city. The two data columns are: Average House Selling Price, and Number of Houses Sold. When I put both of these data fields into the PivotTable Wizard, they are listed below each other so that each Month occupies two rows, but each city occupies one column. I want the two data fields beside each other so that each month only occupies one row, but there are two data columns for each City.

View 2 Replies View Related

Listbox Result To Pivottable Current Page Field

Aug 10, 2007

I am thinking of using a listbox so a user can select a product to graph. What I want to know, in VBA, is how do I use the selection the user makes and use it in a pivot table current page field? The listbox returns a number whereas I want the text. I can use the number when it is linked to a cell and a vlookup to get the text but how do I get the text into the current page field?

View 2 Replies View Related

Exclude Network Path In PivotTable Based On Sharepoint

Sep 4, 2007

I have an excel file linked to a sharepoint 2007 network space. Everytime I open the file, my pivot tables will not refresh, because the source of the pivottable seems to update automatically to include the network drive name on them which somehow causes a problem. Is there anyway to solve this? I put a snippet of the source in the pivottable as an example.

"http ... /PMO/Shared%20Documents/ Finance/Budget/2008/2008%20What%20If%20Budget.xls'!=WhatIf"

View 3 Replies View Related

ADVERTISEMENT