Displaying pivot cell underlying data

One of the really neat features of a pivot is that by clicking on a pivot
cell, it displays a new worksheet of the underlying data that makes up the
cell value.... Unfortunately, this is having unpredictable results - some
times many more rows are returned than the value in the pivot cell.
Can anyone tell me why this is - or am I doing something dumb...?
--
Mike Watkins

The list that's created should only include the records summarized in
the cell that you double-clicked. Can you give an example of the results
that you're seeing?

watkincm wrote:
> One of the really neat features of a pivot is that by clicking on a pivot
> cell, it displays a new worksheet of the underlying data that makes up the
> cell value.... Unfortunately, this is having unpredictable results - some
> times many more rows are returned than the value in the pivot cell.
> Can anyone tell me why this is - or am I doing something dumb...?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Debra,
This is a mock-up representation of the pivot result...
C D E
F G
18 Geo 1 4 30 79 193 314
19 Geo 2 7 60 140 272 428
20 Geo 3 2 25 165 140 280

As an example, if I click on cell C19, while the displayed value is 7, 18
rows are produced - it's the same 18 for each cell in that column.
In col. D, it displays 627 rows for each cell in that pivot col...
Is this any help?
--
Mike Watkins


"Debra Dalgleish" wrote:

> The list that's created should only include the records summarized in
> the cell that you double-clicked. Can you give an example of the results
> that you're seeing?
>
> watkincm wrote:
> > One of the really neat features of a pivot is that by clicking on a pivot
> > cell, it displays a new worksheet of the underlying data that makes up the
> > cell value.... Unfortunately, this is having unpredictable results - some
> > times many more rows are returned than the value in the pivot cell.
> > Can anyone tell me why this is - or am I doing something dumb...?
>
>
> --
> Debra Dalgleish
> Contextures
> http://www.contextures.com/tiptech.html
>
>

It's hard to tell what the data mean. If the 7 is a sum, it could be the
total of 18 rows that contain positive and negative values in the field
in column C.
But if Geo 1, Geo 2 and Geo 3 are different items in a field,
double-clicking on cell C18 should give you records for Geo 1. They
should be different than what you'd get by double-clicking on C19.

watkincm wrote:
> Debra,
> This is a mock-up representation of the pivot result...
> C D E
> F G
> 18 Geo 1 4 30 79 193 314
> 19 Geo 2 7 60 140 272 428
> 20 Geo 3 2 25 165 140 280
>
> As an example, if I click on cell C19, while the displayed value is 7, 18
> rows are produced - it's the same 18 for each cell in that column.
> In col. D, it displays 627 rows for each cell in that pivot col...
> Is this any help?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

The first and third values in the column are "Count", while the middle value
is "Count Nums".
It turns out that the rows being returned tie to another, initial pivot in
the workbook (there are 3). The initial pivot displays rows correctly. The
other two pivot tables both exhibit the same problem and are both "based on
the same data" as the first - to save space!
Is this a characteristic of "cloned" tables? If I wish these tables to
display correctly, should I create a new workbook each time...?

--
Mike Watkins


"Debra Dalgleish" wrote:

> It's hard to tell what the data mean. If the 7 is a sum, it could be the
> total of 18 rows that contain positive and negative values in the field
> in column C.
> But if Geo 1, Geo 2 and Geo 3 are different items in a field,
> double-clicking on cell C18 should give you records for Geo 1. They
> should be different than what you'd get by double-clicking on C19.
>
> watkincm wrote:
> > Debra,
> > This is a mock-up representation of the pivot result...
> > C D E
> > F G
> > 18 Geo 1 4 30 79 193 314
> > 19 Geo 2 7 60 140 272 428
> > 20 Geo 3 2 25 165 140 280
> >
> > As an example, if I click on cell C19, while the displayed value is 7, 18
> > rows are produced - it's the same 18 for each cell in that column.
> > In col. D, it displays 627 rows for each cell in that pivot col...
> > Is this any help?
>
>
> --
> Debra Dalgleish
> Contextures
> http://www.contextures.com/tiptech.html
>
>

I've never seen that behaviour, even in a pivot table that's based on
another pivot table. What version of Excel are you using?

watkincm wrote:
> The first and third values in the column are "Count", while the middle value
> is "Count Nums".
> It turns out that the rows being returned tie to another, initial pivot in
> the workbook (there are 3). The initial pivot displays rows correctly. The
> other two pivot tables both exhibit the same problem and are both "based on
> the same data" as the first - to save space!
> Is this a characteristic of "cloned" tables? If I wish these tables to
> display correctly, should I create a new workbook each time...?
>


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Office XP - Excel 2002
--
Mike Watkins


"Debra Dalgleish" wrote:

> I've never seen that behaviour, even in a pivot table that's based on
> another pivot table. What version of Excel are you using?
>
> watkincm wrote:
> > The first and third values in the column are "Count", while the middle value
> > is "Count Nums".
> > It turns out that the rows being returned tie to another, initial pivot in
> > the workbook (there are 3). The initial pivot displays rows correctly. The
> > other two pivot tables both exhibit the same problem and are both "based on
> > the same data" as the first - to save space!
> > Is this a characteristic of "cloned" tables? If I wish these tables to
> > display correctly, should I create a new workbook each time...?
> >
>
>
> --
> Debra Dalgleish
> Contextures
> http://www.contextures.com/tiptech.html
>
>

Is the source data external, or a table in Excel?

watkincm wrote:
> Office XP - Excel 2002


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Excel worksheets
btw... I really appreciate your attention to this. Thank you
--
Mike Watkins


"Debra Dalgleish" wrote:

> Is the source data external, or a table in Excel?
>
> watkincm wrote:
> > Office XP - Excel 2002
>
>
> --
> Debra Dalgleish
> Contextures
> http://www.contextures.com/tiptech.html
>
>

If you copy the problem pivot tables to a new worksheet, do you still
have the same result when you double-click a cell?


watkincm wrote:
> Excel worksheets
> btw... I really appreciate your attention to this. Thank you


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

I have a related question. Is there a way to have the underlying data go into
just one new worksheet when you double-click/drill-down on a cell? Right now
Excel adds a new worksheet for every cell you drill-down on. Thanks!
Cynthia

"watkincm" wrote:

> One of the really neat features of a pivot is that by clicking on a pivot
> cell, it displays a new worksheet of the underlying data that makes up the
> cell value.... Unfortunately, this is having unpredictable results - some
> times many more rows are returned than the value in the pivot cell.
> Can anyone tell me why this is - or am I doing something dumb...?
> --
> Mike Watkins

No, you can't select a destination for the data that's created. Excel
adds a sheet, and places the data there.

lovemuch wrote:
> I have a related question. Is there a way to have the underlying data go into
> just one new worksheet when you double-click/drill-down on a cell? Right now
> Excel adds a new worksheet for every cell you drill-down on. Thanks!
> Cynthia
>
> "watkincm" wrote:
>
>
>>One of the really neat features of a pivot is that by clicking on a pivot
>>cell, it displays a new worksheet of the underlying data that makes up the
>>cell value.... Unfortunately, this is having unpredictable results - some
>>times many more rows are returned than the value in the pivot cell.
>>Can anyone tell me why this is - or am I doing something dumb...?
>>--
>>Mike Watkins
>


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

BTW...I really like your website. I've been doing quite a bit of research
lately for budget spreadsheets for my employer. Most sites are very
unorganized with too much information and business. Your's is laid out in a
clear and very useful manner. Cynthia

"Debra Dalgleish" wrote:

> No, you can't select a destination for the data that's created. Excel
> adds a sheet, and places the data there.
>
> lovemuch wrote:
> > I have a related question. Is there a way to have the underlying data go into
> > just one new worksheet when you double-click/drill-down on a cell? Right now
> > Excel adds a new worksheet for every cell you drill-down on. Thanks!
> > Cynthia
> >
> > "watkincm" wrote:
> >
> >
> >>One of the really neat features of a pivot is that by clicking on a pivot
> >>cell, it displays a new worksheet of the underlying data that makes up the
> >>cell value.... Unfortunately, this is having unpredictable results - some
> >>times many more rows are returned than the value in the pivot cell.
> >>Can anyone tell me why this is - or am I doing something dumb...?
> >>--
> >>Mike Watkins
> >
>
>
> --
> Debra Dalgleish
> Contextures
> http://www.contextures.com/tiptech.html
>
>

Thank you! I'm glad you like it.

lovemuch wrote:
> BTW...I really like your website. I've been doing quite a bit of research
> lately for budget spreadsheets for my employer. Most sites are very
> unorganized with too much information and business. Your's is laid out in a
> clear and very useful manner. Cynthia
>
> "Debra Dalgleish" wrote:
>
>
>>No, you can't select a destination for the data that's created. Excel
>>adds a sheet, and places the data there.
>>
>>lovemuch wrote:
>>
>>>I have a related question. Is there a way to have the underlying data go into
>>>just one new worksheet when you double-click/drill-down on a cell? Right now
>>>Excel adds a new worksheet for every cell you drill-down on. Thanks!
>>>Cynthia
>>>
>>>"watkincm" wrote:
>>>
>>>
>>>
>>>>One of the really neat features of a pivot is that by clicking on a pivot
>>>>cell, it displays a new worksheet of the underlying data that makes up the
>>>>cell value.... Unfortunately, this is having unpredictable results - some
>>>>times many more rows are returned than the value in the pivot cell.
>>>>Can anyone tell me why this is - or am I doing something dumb...?
>>>>--
>>>>Mike Watkins
>>>
>>
>>--
>>Debra Dalgleish
>>Contextures
>>http://www.contextures.com/tiptech.html
>>
>>
>


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Debra....
Yes - I get the identical problem - same rows returned...
Is the format of the cells within the worksheet the cause of pivot confusion?
For example, in many of the formulae that generate the cell value, if the
value is 0, it is told to return a blank (actually ""). Except, if one uses
the isblank() test on the cell - it returns false! So, the cells may have a
value just not a positive or negative number.
In those cases where Count nums is used in the Pivot field settings, it
would presumably calculate the correct value but would display all rows that
have any value when double clicked...?
This still doesn't account for why some of the "Count" field settings also
display incorrectly - or does it?
--
Mike Watkins


"Debra Dalgleish" wrote:

> If you copy the problem pivot tables to a new worksheet, do you still
> have the same result when you double-click a cell?
>
>
> watkincm wrote:
> > Excel worksheets
> > btw... I really appreciate your attention to this. Thank you
>
>
> --
> Debra Dalgleish
> Contextures
> http://www.contextures.com/tiptech.html
>
>

If a formula returns "", it's an empty string, not a blank cell, and
would be included in a Count, but not in a Count Nums.

So the number of rows returned won't always match the count shown in the
pivot table.

watkincm wrote:
> Debra....
> Yes - I get the identical problem - same rows returned...
> Is the format of the cells within the worksheet the cause of pivot confusion?
> For example, in many of the formulae that generate the cell value, if the
> value is 0, it is told to return a blank (actually ""). Except, if one uses
> the isblank() test on the cell - it returns false! So, the cells may have a
> value just not a positive or negative number.
> In those cases where Count nums is used in the Pivot field settings, it
> would presumably calculate the correct value but would display all rows that
> have any value when double clicked...?
> This still doesn't account for why some of the "Count" field settings also
> display incorrectly - or does it?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

In other words, the pivot is responding according to spec - as designed. Now
I need to find a way to insert blank cells rather than empty strings in the
data through these formulae. Is there a way to do that? Nothing I have tried
so far has the desired effect.
--
Mike Watkins


"Debra Dalgleish" wrote:

> If a formula returns "", it's an empty string, not a blank cell, and
> would be included in a Count, but not in a Count Nums.
>
> So the number of rows returned won't always match the count shown in the
> pivot table.
>
> watkincm wrote:
> > Debra....
> > Yes - I get the identical problem - same rows returned...
> > Is the format of the cells within the worksheet the cause of pivot confusion?
> > For example, in many of the formulae that generate the cell value, if the
> > value is 0, it is told to return a blank (actually ""). Except, if one uses
> > the isblank() test on the cell - it returns false! So, the cells may have a
> > value just not a positive or negative number.
> > In those cases where Count nums is used in the Pivot field settings, it
> > would presumably calculate the correct value but would display all rows that
> > have any value when double clicked...?
> > This still doesn't account for why some of the "Count" field settings also
> > display incorrectly - or does it?
>
>
> --
> Debra Dalgleish
> Contextures
> http://www.contextures.com/tiptech.html
>
>

A formula can't return a blank cell. Perhaps you could use programming
to clear the cells before you update the pivot table.

watkincm wrote:
> In other words, the pivot is responding according to spec - as designed. Now
> I need to find a way to insert blank cells rather than empty strings in the
> data through these formulae. Is there a way to do that? Nothing I have tried
> so far has the desired effect.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Built a new copy of the spreadsheet - wrote a vba macro to "empty" all blank
cells of invisible values - verified to ensure they are indeed empty by using
"Count"/"CountA" and "Isblank" ...
Unfortunately, the pivot table is still returning superfluous rows on double
clicking the cell. Some work fine - others do not.
Any clues, anyone...?

--
Mike Watkins


"Debra Dalgleish" wrote:

> A formula can't return a blank cell. Perhaps you could use programming
> to clear the cells before you update the pivot table.
>
> watkincm wrote:
> > In other words, the pivot is responding according to spec - as designed. Now
> > I need to find a way to insert blank cells rather than empty strings in the
> > data through these formulae. Is there a way to do that? Nothing I have tried
> > so far has the desired effect.
>
>
> --
> Debra Dalgleish
> Contextures
> http://www.contextures.com/tiptech.html
>
>