Is it possible in some way to fix the structure of a pivot table in excel or
even in Access so that I have a familiar layout even though the data may
vary from differences in the data. By implication, thefore to show empty
colums/rows somehow. This is very important to my situation.
I couldn't think of any way of changing the format of the
However to solve your problem, you could have your own
format in a different sheet (within the same file) and
link the pivet table using VLOOKUP( formula to get the
numbers to the right place.
You could use refresh data to upda...Does anyone know the PivotTable Refresh Event? #2
Thank you Debra it worked fine for me
JohnD'Elia's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1531
View this thread: http://www.excelforum.com/showthread.php?threadid=26930
You're welcome. Thanks for letting me know that it worked.
> Thank you Debra it worked fine for me.
Excel FAQ, Tips & Book List
...How do I add new data to a pivottable/chart?
I have a spreadsheet that I add new data to weekly. Is there a way to have
the pivottables/charts refresh to include this data without recreating them?
If I try to do extend the range of cells used to create them originally, I am
unable to group dates (which I need to do) on the chart because of blank
You can use a dynamic range as the pivot table source, and it will
automatically expand as rows or columns are added. There are
When you refresh the pivot table, all the new data will be included.
Brian Lee wr...pivottables
I created a pivot from a dataset. This was no problem at all. In a
later stadium I changed the recordset where the pivottable is based on.
For example i replaced all the records containing pete into mike in the
column <NAME>. When refreshing the pivot it now contains both mike and
pete. Pete is redundant, because i no longer exists. How do I get rid
Just drag the field out of the Pivot Table, then do a Refresh, then drag
it back again
"lennert" <firstname.lastname@example.org> wrote in message
How do I use more than one Summary function for a PivotTable data field?
In the Pivot Wizard, add another copy of the field to the data area
Double-click on the new field, and under 'Summarize by', choose one of
> How do I use more than one Summary function for a PivotTable data field?
Excel FAQ, Tips & Book List
...PivotTable Column Field Values
The column field list of my PivotTable (when one clicks on the
drop-down arrow) is populated with a list of items which no longer
exist in the data table from the data is drawn.
Is it possible to reset these values so that only valid data can be
Please let me know if I have not explained the problem adequqtely.
There are instructions here for clearing old items from a PivotTable
Michael Rekas wrote:
> The column field list of my PivotTable (when one clicks on the
> drop-down arrow) ...pivottable
Is there anybody how knows what the shortcut is to deselect fields in a
pivottable? (I have a pivottable with 1000 possiblities for a field, but I
only need some of them)
Excel 2002 has a Show All checkbox to show or hide items in a field. For
earlier versions, you can use code. There are examples here:
> Is there anybody how knows what the shortcut is to deselect fields in a
> pivottable? (I have a pivottable with 1000 possiblities for a field, but I
> only need some of them)
Debra Dalg...PivotTable canned functions
I am using Excel 2000 (9.0.2720).
In my work I often faced with the task of creating ratios of
CurrentListValue / CurrentSaleValue then need to perform various
calculations on those ratios to identify and conclude on Central Tendencies
(among other things) within categories from a parent array. The list of
functions available for PivotTable calculation (I am assuming under the hood
these are Array Functions) is very narrow. Only providing for Average,
Standard Deviation, Min, Max, etc.
Is there a way to add to this list, such as but not limited to, Average
Deviation, Median, Geometr...PivotTable ?
I want to ask a general question ? What is the use of a pivottable ? Why is
it called "pivot" ? Actually, I can do the analysis on the original table
On Mar 13, 1:52=A0pm, "Carlson" <davidcarl...@yahoo.com> wrote:
> I want to ask a general question ? What is the use of a pivottable ? Why =
> it called "pivot" =A0? =A0Actually, I can do the analysis on the original=
a Pivot table is a very useful tool to analyse data columns. The name
stems from the technical field: a pivot...Incorrect Field Names Appear in PivotTable Field Dialog Box
I have found that items deleted from my source, standard worksheet,
are still appearing in the Pivot Table Drop down list. I have applied
the sollution in MS202232 but it did not clearup the outdated data. I
have refreashed the pivot table before and after runing the MACRO
Not sure what else can be done, I continue to search groups and MS.
This is the code for the MACRO based MS202232:
On Error Resume Next
For Each pvtfield In
For Each pvtitem In pvtfield.PivotItem...Pivottables
Is it possible to use a pivottable to comapre 2 data
sources and produce a pivottable of variances? Is so How?
This may work: I've copied|pasted one set of data under the other.
Then add an additional column to serve as an indicator (old or new).
Then created the pivottable with that indicator as a column field. Then I'd
usually convert to values and add a few formulas to look for differences. Apply
Data|filter|autofilter to find them.
Another option that may not work.
I've changed the values of the "new" data to negative
put -1 in an empty cell and...Pivottable Problem
Excel save as web page.... .xml
I have setup a series of pivottable that work fine through a web browser on
my local pc drive. But when I have some access through the network cross
mounted drive I get an error "error opening data file
"file//\\nca-exch\......data001.xml". Can someone help me with this. Why
does the file access try file://\\?
...PivotTable based on multiple ranges or tables
If I have multiple ranges or tables on one worksheet, can I create a Pivot
Table based off of more than one range or table? For example, if a worksheet
has the following tables/ranges: office1, office2, office3, and each
table/range contains a list of equipment for each of those offices, can I use
create a PivotTable that shows the "telephone" equipment from all three
Step 1 of the pivot table creation wizard has 3 different options for source
data. The third option is Multilple Consolidation Ranges whixh will allow you
to consolidate from multiple sheets. Y...Pivottable
Is it possible to do the folliwng in excel using pivot tables. I have data
from a slaughterhouse. It consists of: date; body part; sold; purchased. Is
it possible to have a pivot table presneting: body parts in columns, date in
rows as well as opening balance, total sold, total purchased and closing
balance for particular day. I don't know how to calculate opening balance
that includes closing balance from previous days? Any idea?
On Jul 20, 7:13=A0pm, "IgorM" <ig...@live.com> wrote:
> Is it possible to do the folliwng in exce...Pivottable
I have a pivot table with two levels of columns headings..so for example
I have Period of the Month...and then under that...week in period...each
period has 4 weeks..
but sometimes I may only have 1 week in a period..
how can i read the pivot table and determine how many weeks in a period are
I am willing to send the file if you reply with an email address
> I have a pivot table with two levels of columns headings..so for example
> I have Period of the Month...and then under that...week in perio...How to get the PivotTable report info that feeds another PivotTable report.
A pivot table can get it's data from another pivot table as so:
Right click in the pivot table and select PivotTable Wizard
Click the < Back button
Click the next < Back button
Select the "Another PivotTable report or PivotChart report" radio button and
click Next >
Select the pivot table containing the same data and click Finish
What I want to get is the source pivot table for a pivot table. I guess I'm
looking for the "parent" of the "child" pivot table :-) How can I do this in
Here's the macro recording of the above steps but it di...PivotTables
I've seena bit of talk about pivotTables and would like to create something using some data I have? Is there a web page Ican check out to get an idiotproof guide and information? Thanks
> I've seena bit of talk about pivotTables and would like to create
> something using some data I have? Is there a web page Ican check out
> to get an idiotproof guide and information? Thanks
Thanks Frank - As ususal your support is appreciated. J
...Drill Down in PivotTables
I have a data table listing various attributes (columns) of sale items
(rows). I have created a pivot table on this array. In the pivot table I
have asked for row data on 'size' and column data on 'category' then data on
average of 'price'. All works fine except when selecting the drop down on
'category' I cannot UNCHECK items in the resulting listbox. This happens
periodically with any PivotTable I make and seems to be no ryme or reason
behind this frustrating fallderall!
Under 'Table Options' the 'Enable Drilldown' checkbox is checke...PivotTable Calculated Item error
I'm new to working with calculated items in PivotTables, and I've run
into a bit of a snag.
My PivotTable is built off of a data set with a start and stop time (in
military time) for a number of workers. Included in the data set as
well is a worktype. I have a calculated field in my Pivot which returns
the # of hours worked by way of the following formula:
I have this data displayed by work type and date on the top, and by 1/2
hour interval of the start time on the left (the data set as well is
restricted to half hour intervals). This part works great!
For ce...Pivottable trauma
I'm looking to extract data from a Pivot table to a new spreadsheet, i
order to not have to type huge numbers of formulae.
i'm using the index formula in combination with hte match formul
followed by the getpivotdata formuala.
I am now very stuck can anyone help?
Message posted from http://www.ExcelForum.com
What are you trying extract, why are you using index/match and
getpivotdata, and where are you getting stuck?
didsburyalex < wrote:
> I'm looking to extract data from a Pivot table to a new spreadsheet, in
> order to not...Pivottable
I have a list of Parts (1000's) in rows and dates (dd/mm/yyy) across the
cols. I need to view several years of data grouped by Qtrs and Months. As
Excel XP is currently limited to 256 cols It will not complete the
pivottable. How can I group all my data on 1 worksheet? I hope this is clear.
...Spawn pivottable sheet
how could spawn data on double-click on a new sheet over a pivottable in
In Excel 2003 it's possible, but are there any options in Excel 2000?
Thanks very much.
If you double-click on a pivot table cell in Excel 2000, it should
create a new sheet, with the source data for that cell.
Juan Manuel Porras Gálvez wrote:
> Hello all!!,
> how could spawn data on double-click on a new sheet over a pivottable in
> Excel 2000?.
> In Excel 2003 it's possible, but are there any options in Excel 2000?
> Thanks very much.
...Creating a PivotTable Report from an Another PivotTable Report
When you begin the pivot table wizard there are a total of four choices under 'What is the data you would like to analyze?'. The fourth (Another PivotTable Report or Pivot Chart Report) is the one that i would like to use and the option button is always greyed out and you are unable to select it. How do I go about making it possible for me to select this option. I am using Microsoft Excel 2002. Can anybody help me
Select an area that includes the Pivot table report you want to include
"bcpaulus" <email@example.com> wrote in message
news:1FC35CE...PivotTable in PowerPoint
I am attempting to place a Pivot Table in my presentation that is linked to
an excel spreadsheet. However, there is no option for me to do this. I have
attempted to copy and paste, insert a table, and I have gone to the Developer
option and the "more controls" tab, but the Microsoft Office PivotTable
option is not available. Is this hidden somewhere or is this possible?
Thank you in advance!!!
...PivotTable: Customized Calculations
I have salary data in an EXCEL spreadsheet (each row
contains the salary for one individual). I wish to
construct a PivotTable that counts the number of
individuals in each of several salary intervals that I
would specify ($0 - $19,999; $20,000 - $29,000, etc.) How
do I do this? I believe that reporting on such customized
calculations are possible, but I can't find any explicit
guidance. Thanks for your help.
You could add a column to the data table, and calculate the salary
Create a lookup table with the categories, e.g.: