Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Pivot Table Creates Extra Data In Fields

I'm trying to create a pivot table, but there is a problem. Although my data only has 2 years (1996 and 1997), it creates a new year it calls "blank," and a lot of the data that is supposed to be under 1996 and 1997 appears under this "blank" year. How do I fix this? See the attachments for the data and pivot table.

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Pivot Table, Calculated Item.... No Data, Still Creates A Row
I have a Pivot table.

Rows: City, Restaurant, Cashflow
Column: Date (Month/Year)
DataField: $Amount

"City" might be 7 cities
"Restaurant" may be 32 restaurants, some in some cities; but not other
"CashFlow" can be either "Revenue" or "Expense"

The layout of the Pivot table is nice, only shows the Rows where a State, Restaurant, and CashFlow entry exists for at least one Date on that row. And there are no extraneous rows for combinations that don't exist.

Now.... the trouble...

I added a Calculated Item, "Profit" which = "Revenue" - "Expenses".
Now, every possible combination of State and Restaurant appears in the pivot table displayed. Only the "Profit" (calculated item) is shown for the previously hidden rows; and of course it's "$0.00" since there are no "Revenue" or "Expense" entries.

How can I get the Pivot table to not display the results of a Calculated Item row, when there are no entries otherwise for that row combination?

Is there a way to condition the Calculated Item to not calculate if there are no data entries in the addends for a particular combination?

View Replies!   View Related
Removing Data Fields With Vba In Pivot Table
I have written code to add datafiels to pivot table but I need to remove the ones that are their prior to adding.

Does anyone have a technique using vba to do this.

View Replies!   View Related
Formula To Handle 5 Fields Of Data To Avoid Creating Extra Columns?
I am inputting 5 variables in columns A through E:

Family (Y/N)
Adult (1/0)
Child (1/0)
Date of Birth

I am attempting to generate:

Age Males in families sorted into age brackets
Age Females in families sorted into age brackets
Age Males single sorted into age brackets
Age Females single sorted into age brackets

I did this rather simplistically generating 4 columns (F through I) and then using COUNTIF (although I know there is a better way than the lame way I used COUNTIF but that is not the point right now).

However, my question is, can I generate the data described above (located in D13:I22 on my spreadsheet) without going through the messy, cumbersome step of creating extra columns?

View Replies!   View Related
Count Pivot Fields In Pivot Table
I am trying to find a way to count the total number of pivot fields in a pivot table so I can remove ghost pivot items that are no longer in the pivot table data. My code for this subroutine is as follows;

Sub RemoveGhostPivotItems()
Dim ghost As PivotItem
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables(1)
pt.ManualUpdate = True
For Count = 1 To 10
On Error Resume Next
For Each ghost In pt.PivotFields(Count).PivotItems
Next ghost
Next Count
pt.ManualUpdate = False
End Sub

My code makes an assumption that I have 10 Pivot Fields or less. It would be nice to actually know the number of Pivot Fields so my "For Count" Loop would be more efficient. In otherwords;..............

View Replies!   View Related
Formatting Pivot Table Fields
I'm trying to loop through all pivot fields in a table and change the format to be 0 decimals and comma seperated.

Here's my

View Replies!   View Related
Pivot Table - Hiding Fields
I have a question regarding pivot tables. how do I hide a field but still maintain that filter? I have a field in the Column Fields titled "International". Right now I've filtered out only the YES entries, but I don't want to see a "YES" column beside each. When I try to hide the column the filter is reset and all entries, international or not, are seen. Can I hide the column but maintain its filter?

View Replies!   View Related
Pivot Table And Counting Unique Fields
Found what it think is the correct methodology (Pivot table) for doing this, but I'm struggling to apply it.

I have a very simple excel 2007 document that has one column with multiple rows;


I would like the easiest way to count the total of each and sort in highest first,


red 4
blue 3
Blue 2
green 2

View Replies!   View Related
Hide/Show Pivot Table Fields
Im using the following VBA code, but the on error resume does nothing, excel throws out the error 1004 "Unable to get the pivotfields property of the pivottable class" (i know why the error is caused and how to fix it but my error trapping wont work). Is there some option which i have turned off which prevents me from trapping errors?

Dim ws As Worksheet
Set ws = Excel.Sheets("Sheet1")
On Error Goto err:
If ws. Range("IV1").Value = "dog" Then
ws.PivotTables("PivotTable1").PivotFields("Price Euros").Orientation = xlHidden
ws.PivotTables("PivotTable1").PivotFields("Price Dollars").Orientation = xlHidden
End If

msgbox "invalid"

View Replies!   View Related
Reset Pivot Table Fields In Multiple Sheets
I have pivot tables in diff pages
im trying to make a button to reset all the fields in all the pivot tables..

i tried recording a macro to do this but it only works for page fields..
it wont work for the column fields.. act, adopt and check are page fields at the top... region is a column field ... goto_AQcheck is a macro to switch to that sheet which is assigned to another button....

this isthe code it gave:

View Replies!   View Related
Using References In Pivot Table Calculated Fields Formula
Worksheet A contains two columns, that maps individual operations to their unitary cost:
COLUMN 1 = a list of operations
COLUMN 2 = the cost associated with each operation.

I can do a vlookup on this worksheet to retrieve the cost of each individual operation.

On another worksheet, I have a pivot table with a field that produces the sum of operations performed by type of operation, eg.

operation 1 was performed 5 times
operation 2 was performed 7 times
operation 3 was performed 4 times

I want to add a calculated field in the pivottable, that would output the total cost per operation. The formula for the calculated field in thepivot table would be
= operation * vlookup("name of operation","range for lookup table",2,0)

... unfortunately, "references, names and arrays are not supported in pivottable formulas".

View Replies!   View Related
Pivot Table Fields, Based On Column References
I have a pivot table which draws data automatically from a database

What I would like it for the customer field of the pivot table to only equal the customers which are present in another worksheet (Column A:A)

View Replies!   View Related
Listing Pivot Table Field List Fields
Is there any code or way to generate the compete list of fields that are selectable from the " PivotTable Field list"?

I have various cubes I need to check and compile the list of fields for each.

View Replies!   View Related
Macro To Print Pivot Table Page Fields
I want to print out, for a range of 'Page Fields', from a Pivot table automatically.

The Macro must be able to:

Automatically go through and select the 'Page Fields' in the pivot table

Select the print area range automatically, which will change each time the pivot is refreshed/reselected.

Set the print area to fit the page for each category selected in the pivot table

Finally print.

Can't find code for this anywhere, but i must admit got fed up with looking

View Replies!   View Related
Test/Compare Pivot Table Fields Macro Code
I wrote a macro for a pivot table.

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Export!R1C1:R3000C53").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable4", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable4").AddDataField ActiveSheet.PivotTables( _
"PivotTable4").PivotFields("Reference"), "Count of Reference", xlCount
With ActiveSheet.PivotTables("PivotTable4").PivotFields("ACN received date")
.Orientation = xlRowField
.Position = 1
End With

What i want to know is if there is a code line to test if "Non Processed" value exists in column "Status" and then hide the value.

Now, if the macro does not find one value just gives me error.

View Replies!   View Related
Don't Show Blank Pivot Table Fields In Drop-downs
I am creating a pivot table from region, which some of the rows are blank.

Is there any possibility that the pivot table not show the blank in its dropdown combobox?

View Replies!   View Related
Pivot Table Query: Make A Pivot Table To Summarise The Data
attached is a spreadsheet 6 people in my area use daily(ive copied and pasted the sheet in question to a new worksheet, as the file was too big). Ive been trying for about 3 days now to make a pivot table to summarise this data.

View Replies!   View Related
Conditionally Hide Pivot Data Fields
I have pivot tables with one row field and two or three data fields. I want to be able to quickly hide rows that do not have entries in *all* data fields.

I thought of auto- filtering my data source, but don't think that would be practical as I want to have several tables powering off of the same source data (with many columns), and also the data source is auto-filled with API... Is there a way to traverse the rowfields with vba and hide them if there isn't an entry for every data field? Or a better way than both of those?

View Replies!   View Related
Import Data From Access Table To Pivot Table - Enable Auto Refresh
I have enable Refresh on Open for my excel pivot table, but user need to click "Enable Automatic Refresh" , only solution i came across is to change the registry setting. Which i dont have access to edit registry(admin disable the access).

Alternate solution i try to use Access macro to automate the process and use Outputto save it as a excel file A. Then use excel file B to update pivot table from excel file A.(as excel A data is always latest)
The problem is i will get "....A file name already you want to overwrite.." prompt.
Which defeat the automate process.

Any other solution to enable the automatic refresh on open the excel workbook?

Or Access can overwrite the exist file or save it as another file name with timestamp ?

View Replies!   View Related
Pivot Table An Extract Of Each Data Contained In This Table
i have a pivot table an extract of each data contained in this table.

[img]Count of NAMdate
SERVICENAM12-oct10-déc11-décGrand Total
Commercial-laura Totalgh11

custody-jonathan Totalgh1113

settlement-ludovic Totalgh11

SPQC-elodie Totalgh112

Grand Total1337

View Replies!   View Related
Create Pivot Table: Cannot Open Pivot Table Source File
I'm trying to write a macro that will create a pivot table, and am getting an Error code 1004: Cannot Open Pivot Table Source File "Sheetname". My code is below. I've tried to note what each section does, and it all seems to work well except for the Pivot Table creation.

View Replies!   View Related
Change The Date On One Of The Pivot Table And Pivot Table Match
I have data that develops 3 to 4 pivot table each day. I would like to know if there is a way to change the date on one of the pivot table and have the other pivot tables date change to match with the first pivot table. At this time I am going to all 3 or 4 pivot table to select the correct date. The date is in the page position of the pivot table. I have attached a small sample of the data and the pivot tables.

View Replies!   View Related
Macro: Analyzes Data In 365 Sheets ( Named "1" Through "365") And Creates A Table/report With The Results
I have a procedure that processes data in a spreadsheet. It analyzes data in 365 sheets ( named "1" through "365") and creates a table/report with the results. When I run this on a scaled down version of my workbook (5 instead of 365 worksheets), the result is almost instantaneous. When this is on my normal workbook, the initial processing is about 1 second per day on Day 1, and the last day it speeds up to almost be instantaneous. It is not a linear relationship between the processing time (still working to get more exact timing information). Also, even if I limit my processing to 5 or 10 sheets, Days 1-10 are always slow and days 350-365 are always fast. The code being run is in the structure as shown below:

numSheets = 365
For counter = 1 To numSheets
x = CalculateSomething(counter)

Private Function calculateSomething(counter As Integer) As Integer

Dim strCounter As String
strCounter = counter

With worksheets(strCounter)
For i = 0 To someNumber
For j = 0 To someDifferentNumber
'Data analyzed on worksheet
End With
End Function

The functions obviously aren't copied and pasted/functional, but the relevant efficiency stuff should be there.

View Replies!   View Related
Pivot Table :: How To Get The % Data Above A Particular Value
I have a excel sheet with following 4 columns

Division Name:

Location Name:

Transformer capacity:

Transformer earth resistance:

Now how can I get answer of following queries in Pivot table. (Excel 2007)

1. % of a particular capacity of transformers in a division say % of 400 capacity transformers in all divisions.

2. % number of transformers having Transformer earth resistance value above a particular value in all divisions. Say % of Transformers having Transformer earth resistance above 2.6 in each division.

View Replies!   View Related
Pivot Table That Get Data Dynamically
I wonder if is it possible to get data in the pivot table dynamically?

For example, I do insert data through the userform [ new department] and when I run the report I see the new entered data in the pivot table. will I have this new department title in the list box in the pivot able?

View Replies!   View Related
Pivot Table To Sort Data?
I need to format some information in an Excel file, before exporting it. All the pivot table information I have found online is primarily for account management, using counts and sums. The data is currently in this format:


I need to sort it into this format in another sheet or another area in the same sheet:


View Replies!   View Related
Pivot Table Uses Raw Data
I'm using Excel for bookkeeping and balancing a budget. I've created one sheet for all my raw data and the other is to summarize the data using a pivot table. On the raw data sheet I have labeled my columns for the pivot table. I was hoping that in the pivot table I can select a year and have all the months for that year and data of that year be available, but not other years. I was also trying to have only the month selected of a year selected and that data be available. I also wanted to show an accounting of money spent by item.

Instead, I have a pivot table that is confusing and very unattractive. The example of this will show my limited knowledge in pivot tables. I'm hoping some of you more affluent "guru" members may be able to help me get something usable and presentable.

I tried creating a dynamic named range for the pivot table "BookKeeping" and a dynamic named range for the money out and money in "Accounting", but I'm not sure if I did that right. I did this because more columns will be added over time. This is the reason I'm asking the above two paragraphs.

Should I be using filters some how?

The Summary Sheet also has the balance of available funds that I'm somehow looking to include in this pivot table. Can the pivot table keep a running balance?

View Replies!   View Related
Sorting Data Within A Pivot Table
I can't seem to be able to find out how to sort data within a pivot table.

I have about 1000 different names that have data values for two years. I have a calculated field to show the difference....

View Replies!   View Related
Pivot Table - Only Use Data If Between 2 Dates
I have a checkbook register set up in Excel, and I really like it. I thought it would be good to set up a pivot table to organize all of the entries into an expense report. I have done this for over a year now, and I love everything about it, but here is the problem:

Now that I am getting so many entries, I thought it would be a good feature to limit the pivot table to a date range. I created 2 entry cells at the top of the pivot table to put a start and end date. I would like the pivot table to compare the entries in the register to the dates, and only include the entries between those dates. How do I do this?

View Replies!   View Related
Data Selection In Pivot Table
I am using Excel 2002..

I have a spreadsheet that is used continuously. It is reset each month. My pivot table dropdown selection (Data) has data from previous months. How do I delete this data?

View Replies!   View Related
Aggregating Data Without A Pivot Table
I am trying to create a formula that will aggregate different levels of data without using a pivot table.

For example, i would like for my formula to return the summarized sales of Region 1. I don't want to use the sum formula because I might want to summarize by common division, such as Telecom. To do this I would at least need an input cell to define the level of detail

Region 1Telecom4291
Region 1Devices1032
Region 2Telecom2054
Region 2Devices3952

ive tried using different combinations of IF, VLOOKUP, and SUM statements, but nothing is working yet.

View Replies!   View Related
Grouping Data In A Pivot Table
from the Forum on building a macro and was kindly told that a Pivot Table would do the job better, which it does. However, my table has a column of several dates, I was wondering the best way to group the dates into months rather than showing the full date, eg, September instead of 16/09/08 or August instead of 01/08/08.

View Replies!   View Related
Pivot Table Source Data
I was wondering if anyone knows how to change the souce data for a Pivot Table?
Eg if I have the Pivot table looking at Columns A & B lines 1 - 20 and I want the Pivot to also include Column C and lines 21 - 50, how would I do this? for a chart I can just right click and select the "Source Data" option but it seems that this is not available for Pivot Tables.

View Replies!   View Related
Loads The Data Into The Pivot Table
I have the following code which loads the data into the pivot table.

View Replies!   View Related
Summarize Data In Pivot Table
I have a worksheet with several columns of data that consists of month to date billing, cost, profit, etc. that are tied to job numbers.

I currently have code ( AdvancedFilter) that seperates this data judging by the first two numbers of the job number. For instance it will take all job numbers that start with 11, copy them to a worksheet, print it and then do the same for 12, 13, 21, etc..

View Replies!   View Related
Show Pivot Table Data By Name
I have a list of Insurance payers - 20 or so. They are listed in a Pivot Table on sheet2 with rate data and such. On sheet1 I have a listbox with the Payers listed and can be multi selected by the user. I wish to have the user select some payers in the listbox, goto sheet2 and view the Pivot Table only containing those Payers selected from the listbox.

View Replies!   View Related
Pivot Table Retaining Old Data
I made a spreadsheet and used pivot tables to summarise the data by project numbers (Row field of pivot table). I deleted the orginal data and copied in data from another spreadsheet. When I look at the detail in row field (click on the down arrow of the project button in the row field of the pivot table) it still shows the original project numbers plus the new project numbers. The data displayed in the pivot table looks like it only refers to the new projects but is not correct. Any ideas why the old project numbers still appear? Refreshing the data doesn't help. The only way I can get the correct data is to delete the pivot table and then recreate it.

View Replies!   View Related
Pivot Table Getting Data From Different Datasources
I just recently started programming in macros for my new job and I really enjoy it. But recently I am faced with a problem that I can't find the solution to. Here's what I am trying to do: I want to make a Macro that will make a pivot table from certain column values. I will be running the macro in various Excel files with exact same columns. I m using an inputbox to get the range for the datasource and another inputbox to get the name of the datasource as well.

Dim MyRange As Range
Dim strTablename As String
Set MyRange = Application.InputBox _
(Prompt:="Highlight entire table", Title:="Range for Pivot table", Type:=8)
strTablename = InputBox("Input Name of this Table exactly as it is now")

So my question is how can I get the Range and String recieved from the inputbox to make the pivottable.

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _ ................

View Replies!   View Related
Re-arrange Data In Pivot Table
How do I rearrange the data columns in a pivot table? I have a table with year to date months, open PO's, & budget numbers, but I'd like to change the order in which they show up in the pivot table.

View Replies!   View Related
Process Data In Pivot Table
I have a database which contain many rows and columns. I tried using Pivot Tables to pull out the data from sheet 1 to look like the format in sheet 2, but without success.

View Replies!   View Related
Pivot Table For Changing Data
I'm creating a pivot table based on a section of data that will continually be of variable size. I'm trying to think of a way to command VBA to create a pivot table without giving an absolute reference to the table's source data. I can automatically select all of the data regardless of size, so I'm trying to see if I can command Excel to create a pivot table based on "Selection" instead of an absolute reference, in much the same way I might use Selection.Name instead of specifically defining a name's parameters. This is the text I currently have in VBA for the pivot table.

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"End!R1C1:R424C14").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion10

My current idea is to name the range (a task I *know* how to do relatively) and refer to the named range instead of the RC:RC jumble.

View Replies!   View Related
Repeat Data In Pivot Table
I frequently use a pivot table to extract summaries from a large data set and then use the pivot table output itself as a data table for further analysis. Where a data value in field 1 has many corresponding values in field 2, the field 1 value appears only in the first line and the field 1 cells for the remainer of the rows below appear as blank.

Currently, I copy the pivot table and paste values elsewhere and populate the blank cells with the value above in order to get a complete data set. I would like to find out if there is any way the pivot table can be configured so that where a field 1 value has multiple corresponding items in field 2, each of the cells in field 1 would repeat the relevant value.

View Replies!   View Related
Pivot Table Refresh To Include New Data
I have an existing pivot table that is feeding from data in another sheet.

The data gets updated every month and sometimes their are more columns of data than the previous month. Everytime there are more columns they are not added into the pivot table when i refresh the table even though they are included in the range.

Is there are way the pivot table will add these new columns when i refresh the table without me having to manually drag them in?

View Replies!   View Related
Pivot Table Data Size Differs
I have all of my pivots all set up and all of my charts running from them.

My challenge is in pasting in the new data every day into the master Sheet BECAUSE, in order to group the dates (from format 20/02/08 into Month and Years in the pivot) you have to 'WRAP' the data, instead of simply blocking in all of the columns......

The challenge is that everyday, the data size differs, e.g; one day it might be 2000 rows, and the next 22,000 rows.


View Replies!   View Related
Consolidate Data Into One Worksheet/pivot Table
I have a 208-tab workbook (4 employees with 52 submitted weekly timesheets). I need to consolidate and pivot job costs based on these submitted timesheets. There are two kinds of jobs: a list of products, from which the employee chooses through a drop-down list. There are also "Tracking Job ID" codes that are numbers between 7000 and an unknown ending number, which the employee types into the sheet. In both cases, the % of work performed is multiplied by the employees hourly rate and creates a total.

I need a pivot table that collects these totals from all the sheets.

I tried to use a VBA template from just with a few examples from my workbook, but I don't know how to use VBA, so I failed. The only thing I did was substitute their example sheets with a few sheets from my workbook and re-wrote that part of the array formula.

see attached workbooks.

View Replies!   View Related
Re-Post - Data Repeats In Pivot Table
I don't believe my earlier post was a duplicat thread. I posted the original thread, then posted a LINK to the original thread. I didn't even ask the question in the link, so there was no way someone would have wasted time answering the question from there. I didn't think I was breaching protocal. Anyway, since both instances of the thread were closed, here is the re-post.

I've recently been trying to learn a little about pivot tables. In the example attached, I have 2 worksheets. One holds raw data collected from suppliers, the other holds a default pivot table created from that data.

View Replies!   View Related
Pivot Table Data Source After Email
why the data reference for a pivot table is changing after I email the workbook to a client. The source data and the pivot tables are all part of the same workbook and I can't understand why its saying can't find source data. Excel 2007 is being used on both computers.

View Replies!   View Related
Deleted Data Stays In Pivot Table..
I have been setting up some pivot tables. Down the left side of the pivot table is the values from a column labelled TRACKS. There are about eight different TRACKS. Say Track1, Track2, Track3, etc. I have set the data range up as a dynamic named range so that if more rows or columns are added to the data then the pivot table will automatically incorporate these.

Today I wanted to test that the pivot was working as expected. One of the things I did was stick a few extra values into the TRACKS column on the data source. These were TEST1 and TEST2. These appeared in the TRACKS down the left side of the pivot table as expected.

When I deleted these two test values, however, they would not disappear from the pivot table. There seemed to be nothing I could do to make them go away, except to rebuild the pivot table from scratch.

View Replies!   View Related
Text In The Data Area Of A Pivot Table
How do I display text in the data area of a pivot table?

View Replies!   View Related
Find Out The Source Data From A Pivot Table?
I've got a bit of a problem revealing pivot table source data, so just wondering if anyone know how to reveal it? My colleague sent me the spreadsheet, I can see the pivot table, but I can't find the source data that the pivot table links to.

View Replies!   View Related
Pivot Table Cache :: Get Back Data
Let's say I have a pivot table made from data in another workbook. The other workbook is then deleted. Is there anyway to get the original data back? My particular file is about 10 MB, but if I "save as" it's only 1 MB. Plus, the pivot table "works," so I'm pretty sure Excel still has my data cached somewhere. How can I access it? I'm decent with VBA, so I'm open to any solution at all.

View Replies!   View Related
Copyright © 2005-08, All rights reserved