Percentages in Pivot Tables
I have two pivot tables in my worksheet - one is calculating the % value
correctly, one isn't. Basically the one that isn't is a pivot table
summarising data from three files. The % from the 'Grand Total' row is
actually adding up the percentage column, instead of dividing the two earlier
I don't think I am making myself too clear, but if there is someone who
thinks they could help me and wouldn't mind looking at my spreadsheet, I
would be really grateful as I am tearing my hair out!
Is the % column coming from your source data? If so, instead of usin...Excel pivot table #2
i encountered an error in my pivot table.
i created an olap cube using the analysis manager.
the cube displays the correct data of my measures but on my
pivot report, it displays #N/A....
i need help to fix this one... thanks.... =)
...Contract duplicates in current & historical tables
We seem to be having a problem, we have to 2 tables in , 1st table
contains all current contracts, 2nd table contains all historical
contracts. We are creating a reporting tool that queries first the
current table, then the historical table in order to see all the
revenue but our report generates duplicate contracts (duplicate
revenue). We discovered that the same contract was in both tables, my
question is has anyone experienced such a problem before and how do we
If you are good at SQL, you could create a view of the two tables Joined with
distinct records to eliminate the dup...Pivot tables #39
How do I link 2 pivot tables together? ie. If I filter on one pivot table I
would like the other pivot table to filter to the same criteria. All data is
from the same source.
I suggested the following, when you posted this question a few days ago:
There's a sample file here that changes the page field in all pivot
tables, when the master pivot table is changed:
Under Pivot Tables, look for 'Change Page Field'
Do you try this and have problems?
car...Multiple Consildation Ranges in a Pivot Table.
Once again a bit of help is required. I am using Excel 97 sr2.
I have two worksheets with 5 columns in each. Both sheets have the same headings
Date / Order Ref / Order Type / Container Ref / Container Type.
There are approx 50,000 rows in each sheet.
I am trying to consolidate these two tables into one pivot table.
But I am only getting the following lables to drag an drop into place.
Page / Row / Column / Count
rather than the headings I would like Date, Order Ref etc.
Am I doing something wrong?
If you choose to use multiple consolidation ranges, you won't get the
same pi...Portion of a table will not print
Operating System: Mac OS X 10.5 (Leopard)
I have a table with about 30 rows and eight columns spanning two pages. The margins are set to 1in. or greater all around. On screen the table appears fine. However, the bottom of the first page of the table will not print. In print preview, the bottom quarter of the page appears with cells outlined but with no cell content. When printing, that same portion that shows up with empty cells in the preview is below the bottom margin of the first printed page and does not flow onto the second page - it is not printed at a...Pivot table, data from various spreadsheet
I have data in 3 separate spreadsheets (in the exact
format). I want to have a pivot table that consolidate
data from these sheets.
Is this possible?
Try Debra's page on:
Excel -- Pivot Tables -- Multiple Consolidation Ranges
GMT+8, 1� 22' N 103� 45' E
"Derrick" <email@example.com> wrote in message
> I have data in 3 separate spreadsheets (in the exact
> format). I want to h...Pivot table for worksheet text fields?
Can a pivot table concatenate text that appears in a worksheet?
I often use Excel to lay out questionnaire responses. Pivot tables are
great to calculate and create cross-tabulations of data: Of those who
answered Yes to Question 3, how many also answered No to Question 4?
But I usually include open ended prompts in my questionnaire: Questions 4:
If your answer to Question 3 is Yes, please explain why.
Is there a way to use a Pivot table to concatenate (compile into one cell)
all the text answers given in response to Question 4?
If not, is there some other Excel function (...Deleting rows from two tables
I have two tables both of which contain aprox 2000 rows and 3 columns.
The headings in each column are repectively: tariff level, product
code, product description.
The problem is best illustrated with an example:
Tariff Level Product Code Product description
10% 0236 Animal fats
15% 4579 Industrial Chemicals
12% 2093 Tapes
Tariff Level Product Code Product Description
13% 1463 Meat
13% 4579 Compact disc's
1...PM Report error A get/change first operation on table PM_Distribut
I am getting the following error while trying to print a history report in
A get/change first operation on table PK_Distribution_WORK_OPEN' (45).
Yesterday another user's system hung while posting a marked AP batch. She
ended up shutting down her computer, then we recovered the batch from my
station and all appeared find. I am not sure if these are related.
...Pivot Table question: Creating a new field that calculates the division of one data field by another
I have a pivot table that includes the sum of sales (a data field) and
the count of sales (a data field) by product.
It looks like this.
sum of sales Count of units
Product a 100 8
product b 45 6
I am trying to create another field that divides the sum of sales by
the count of units for each product (i.e. in a fourth column.
Is there an easy way to do this (within the pivot table). I tried
using the calculated field option, but didn't seem to find the way to
Also once I have created a c...CT Tables
I've several of tables loaded into my company database. I am looking at these
tables and they have some interesting fields. Can anyone tell me what modules
these tables are for? I cannot find any information on these tables in GP
tables description. They all start with CT.
Any help will be highly appreciated.
These tables do not appear to be native to Great Plains, unless they are new to
version 8. What third party products do you have installed along with Great
Plains? Any developer worth his salt would ...How can I paste a wide table from Excel into Quark?
With an old version of Excel I had no problem copying wide tables from Excel
into Quark and then reducing them if necessary to fit the margins. Now I have
New Microsoft Excel Worksheet and when I copy and paste a wide table, the
right-hand columns do not appear in Quark. Please help.
Maybe try my webtablemaker at
It will convert your table to a html web page. From there, you could maybe
import it into Quark if it has webpage import?
"Tom Ireland" wrote:
> With an old version of Excel I had no problem copying wide tables from...refresh of pivot table clears fields (column headers)
Pivot report being refreshed on win XP clears Fields (column headers empty).
Basic file works ok with excel 2002 - SP2
Problem occurs (only?) when same file is loaded & pivot refreshed on excel
2002 SP3. As well when refreshed in Macro as manual refresh.
...How 2 set table size?
This is a multi-part message in MIME format.
Hi, I was wondering if someone could point me in the right direction. I =
need to create a table that is 5" x12" , contains 3 columns and 20 rows, =
and I need to lock the cells so they do not expand. Here are my =
How do I specify the table size of 5" x 12"?
How do lock the cell sizes?
...Double Counting in Pivot Tables
I need help :(
The data in my spreadsheet is organized as shown below with each numbe
representing a column
1. Customer Name
2. Name of Customer's Company
3. Name of specific project with customer (for simplicity think Projec
x, Project y...)
4. Internal Consultant assigned to specific engagement (Jim, Jane...)
5. Customer satisfaction rating with our company (Good, Very Good...)
My problem is that I need to sort the data in various ways and I a
running into a double counting issue. For example, three interna
consultants may be assigned to Project x and the customer satisfactio
I am trying to create a pivot table that has some 10k records/rows. The
column being used in the pivot table has mostly unique items... I am getting
the following error msg
"A field in your source data has more unique items than can be used in a
pivot table. Microsoft excel may not be able to create the pivot table or may
create the pivot table without the data from this field"
This started to occur once the record/row length (or I should say the unigue
items) exceeded about 8005. Is there a way to overcome this / or increase the
number of unique items allowed?
Any help woul...How to draw blue borders dynamically like pivot table?
After Added a pivot table onto a sheet, Excel will draw blue borders of its
column area, row area and data area. If you click one of the cells of the
pivot table, the blue borders are showed automatically; if the pivot table is
not focused, the borders disappear. Is there a way to implement similar
effect by programming?
...Update One table from another table
I have to create a separate database for an outsource company where the
records are sent to the 'home' database daily. I need to know if it's
possible to update the outsource table from the home table once processing
has been done. Is it possible to have and update query match primary keys and
update the corresponding fields?
Message posted via AccessMonster.com
...Pivot Tables referring to external data query
In my excel file I am querying external data(an ODBC database) in one sheet.
The query data amounts to over 10,000 rows.
I am using a pivot table to summarize the query data. As my query data
expands, the pivot table range doesn't expand. Is there a setting that
enables the pivot table range to expands with the query data?
When the query is imported, a name should be automatically created in
Excel. You can use that as the pivot table source.
To find the name:
Select the sheet that contains the imported data
Right-click on a cell in the imported data range
Choose Data Range Pr...I want year in one table to be less or equal year in another table
I have some problems writing a query and I hope someone can help me.
I have a database with serveal tables. In one table I have this information,
Lake ID-number, treatment, year for treatment. In another table I have Lake
ID-number, fish species (I am intrested in pike), year when pike is present.
I want to find all lakes that have pike present before the treatment was
done, I want the year in the second table to me less or equal the year in the
first table. Is there a easy way to do this?
Try something like this substituting your table and field names.
S...Data in my pivot table changes to zeros
I have created a pivot table using a dynamic named range as my source.
The source data consists of 52,416 rows and 10 columns showing:
Everything appears as expected when I go through Excel's PivotTabl
wizard but the -Sum of Checkins- and -Sum of Checkouts- (which I plac
in the data area of the PT) always come up as zeros. The PT seems t
have replaced all the correct data in each cell with a zero!
In addition, I have to manually change the fields to "-Sum of-..." a
opposed to the "-Count of-..." ...Combining Tables
I apologize if this has been answered, but I cannot seem to create th
correct search to find it! I have been racking my brain for two days
and am about to give up.
My problem is that I need to combine two tables.
Table 1: Item Number, Item Name
102 Other Thing
Table 2: Item Number, Price
What I want to do is create a single table:
Item Number Item Name Price
102 Other Thing $64.99
Any help is much appreciated!
Message posted from http://www.ExcelForum.com...Comapring rows of a table to multple rows within the same table
I have the following data which I have been tasked with identifying a
primary case for each customer. Unfortunately the data contains duplicate
records such as Case 1 and 2. I would be extremely grateful if you could tell
me how can I compare all the rows related to Customer 1 and flag a case as
the primary case.
CaseID, CustomerID, PrimaryStatus, SecondaryStatus, GroupA, GroupB
1,1,New, Unassigned, RU, HK
2,1,New, Unassigned, RU, HK
3,1,In Progress, Normal Review, RU, HK
4,1,New, Unassigned, IN, UK
5,2,New, Assigned, JP,
6,2,In Progress, Special Review, BR, HK
7,2,Co...Combo Box From Table/Query
I have a table of part numbers using input mask to create format 1234-56-7899.
When I import the list via table or query to the combo box, the drop down
list doesnt show the hyphens, just the 8 consecutive numbers? I have done
this many times with no issues. What have I missed. Thanks in advance.
Message posted via http://www.accessmonster.com
On Fri, 19 Feb 2010 05:07:22 GMT, "Bruister via AccessMonster.com"
>I have a table of part numbers using input mask to create format 1234-56-7899.
>When I import the list via table or query to...