The case of the imperfect Pivot Table

  • Thread starter Thread starter rob nobel
  • Start date Start date
R

rob nobel

I created a data list and 8 pivot tables (all from the 1 pivot table)
related to that list. (I have 1 pivot table for each column)
1. For some strange reason, the data in the second row was not shown in any
of the pivot tables (even after clicking the refresh button many times).
Only after I typed something different into one of the cells in that
offending row and refresh did the correct data appear.
2. I notice too that if the data in all the rows is blank, then all the
fields that the data is based upon are still shown. It seems that a pivot
table will only show correct values if at least one of the row's data cells
has some data in it??
Is there a way to avoid this?
Rob
 
Personally I try never to have blank fields in my raw data if I can help it. I
usually select the entire range of data before creating a Pivot table, do Edit /
Go To / Special / Blanks, type 0 and then hit CTRL+ENTER. This also means that
when I create the PT, my fields will default to SUM instead of COUNT, which I
much prefer. Might not always be appropriate to do that, but I haven't come
across that scenario yet.
 
Thanks for your comments Ken. Good suggestion about the blanks.
But regarding my problem....I may not have explained the situation properly,
so here goes.
The pivot tables are based on a range that covers the full area that data
will be entered. Initually there will may be no data at all at inception of
the file.
In the first column labeled Expense Items, I have a list of expense names
(aprox 50 rows). In the next 8 columns, labeled with an appropriate Account
Name, I put the value "X" in the cell against each row that has an expense
item appropriate to that account. As each account does not have the same
expense items as another, an "X" will be placed in a slightly different spot
from the others.
In fact, one account may not have any expenses related to it at all at
inception. Which creates the problem. As, if there have been no "X"s typed
in for a particular account, ALL the expense items will be shown in that
a/c's Pivot table. Whereas if just one "X" is typed in for that a/c, only
the one expense item related to that "X" will appear in the pivot table.
I drag the Expense Item Column to the "drop row fields here" area and change
the advanced field option to show the top 1 so that all the fields that have
an X aginst that name will be shown with a 1.
I drag the a/c name Columns as one item to the "drop column fields here"
area and select the appropriate account within the pivot table drop down
list.
I drag the values ("X") to the "drop data items here" area.
What I would like to happen of course is that the pivot table not include
any expense items if there is no X aginst that item for that a/c. but I
can't get that to happen.
Rob
 
I think I've solved the problem by dragging the "X" value field to both the
data and the column fields of the pivot table which allows me to deselect
items that have a 0 value.
Rob
 
Back
Top