Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
PC PC is offline
external usenet poster
 
Posts: 3
Default Pivot Table data not matching source

Hit something weird today. We are making a lookup table to summarize a
lenghty list

EmpID
<no column
EmpBen EmpBenAtt

Layout is very important but just to give you an idea.

The concept we are after is they select the EmpID they want from the
drop down and it will give them a list of benefits for that employee.
Now since it is a long list and they're used to a text based system they
are typing the EmpID in. Most of the time this works well - but...

If they type something wrong they get a message "No item of this name
exists in the PivotTable report. Renmae 'X' to 'Y'?" And they hit OK.

At this point the PivotTable data in inaccurate. They've changed an
employee ID to something random and they don't remember what it is or
was (since we figure it out 3 days later).

I check the raw data that the pivot table is based upon and it's correct
- no changes made. So I do a refresh on the PivotTable and the messed
up data remains. I tried clearing Old items as oulined at
http://www.contextures.com/xlPivot04.html but that didn't make any
change.

So.. is there anyway to stop this behavior (protecting the sheet doesn't
work since it kills off needed functionality) OR how do I refresh the
data so that the correct data is displaying not the edited version?

I've only tried this in 2003 and have recreated with all data elements
in a PivotTable in 6 different spread sheets on 2 different computers
(way different models so I know it's not a image issue).


Hope that made sense...

Thanks,
PC_

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Pivot Table data not matching source

I hit something like this recently while working with PivotCharts. When
source data was added and then removed, it remained an option for the Chart,
which seems to be your problem, too. To force the data to remove outdated
entries, I had to go to the table properties and remove the field from the
table. Once I had done this and exited, I updated the table (may not be
necessary) and added the field back to the table. Obviously, this is a bit
cumbersome.

To make it easier, I recorded a macro. I just started the recording, went
through the previous steps, and then stopped it. I attached the macro to a
button, and I put it on the main page. I can't tell if this will work for
you, as I am relatively new to PivotTable and PivotChart applications. Good
luck.

"PC" wrote:

Hit something weird today. We are making a lookup table to summarize a
lenghty list

EmpID
<no column
EmpBen EmpBenAtt

Layout is very important but just to give you an idea.

The concept we are after is they select the EmpID they want from the
drop down and it will give them a list of benefits for that employee.
Now since it is a long list and they're used to a text based system they
are typing the EmpID in. Most of the time this works well - but...

If they type something wrong they get a message "No item of this name
exists in the PivotTable report. Renmae 'X' to 'Y'?" And they hit OK.

At this point the PivotTable data in inaccurate. They've changed an
employee ID to something random and they don't remember what it is or
was (since we figure it out 3 days later).

I check the raw data that the pivot table is based upon and it's correct
- no changes made. So I do a refresh on the PivotTable and the messed
up data remains. I tried clearing Old items as oulined at
http://www.contextures.com/xlPivot04.html but that didn't make any
change.

So.. is there anyway to stop this behavior (protecting the sheet doesn't
work since it kills off needed functionality) OR how do I refresh the
data so that the correct data is displaying not the edited version?

I've only tried this in 2003 and have recreated with all data elements
in a PivotTable in 6 different spread sheets on 2 different computers
(way different models so I know it's not a image issue).


Hope that made sense...

Thanks,
PC_


  #3   Report Post  
Posted to microsoft.public.excel.misc
PC PC is offline
external usenet poster
 
Posts: 3
Default Pivot Table data not matching source

That was a good idea unfortunately it didn't work out for me.

I've event tried refreshing the cache progmatically (same link as
before) but to no avail.

The only thing that's really different from the normal situations is the
value I type in and changed to is 'Y'. 'Y' only exists in my PivotTable
- no where else - never has and never will.

I've tried this so far in Excel 2003 and Excel 2007 b2.
Same results both places.

?B?Wm9ycm9UaGVQaWtpbmc=?=
wrote in
:

I hit something like this recently while working with PivotCharts.
When source data was added and then removed, it remained an option for
the Chart, which seems to be your problem, too. To force the data to
remove outdated entries, I had to go to the table properties and
remove the field from the table. Once I had done this and exited, I
updated the table (may not be necessary) and added the field back to
the table. Obviously, this is a bit cumbersome.

To make it easier, I recorded a macro. I just started the recording,
went through the previous steps, and then stopped it. I attached the
macro to a button, and I put it on the main page. I can't tell if
this will work for you, as I am relatively new to PivotTable and
PivotChart applications. Good luck.

"PC" wrote:

Hit something weird today. We are making a lookup table to summarize
a lenghty list

EmpID
<no column
EmpBen EmpBenAtt

Layout is very important but just to give you an idea.

The concept we are after is they select the EmpID they want from the
drop down and it will give them a list of benefits for that employee.
Now since it is a long list and they're used to a text based system
they are typing the EmpID in. Most of the time this works well -
but...

If they type something wrong they get a message "No item of this name
exists in the PivotTable report. Renmae 'X' to 'Y'?" And they hit
OK.

At this point the PivotTable data in inaccurate. They've changed an
employee ID to something random and they don't remember what it is or
was (since we figure it out 3 days later).

I check the raw data that the pivot table is based upon and it's
correct - no changes made. So I do a refresh on the PivotTable and
the messed up data remains. I tried clearing Old items as oulined at
http://www.contextures.com/xlPivot04.html but that didn't make any
change.

So.. is there anyway to stop this behavior (protecting the sheet
doesn't work since it kills off needed functionality) OR how do I
refresh the data so that the correct data is displaying not the
edited version?

I've only tried this in 2003 and have recreated with all data
elements in a PivotTable in 6 different spread sheets on 2 different
computers (way different models so I know it's not a image issue).


Hope that made sense...

Thanks,
PC_




  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,979
Default Pivot Table data not matching source

There's a feature that resets the captions, in my pivot table add-in,
that you can download he

http://www.contextures.com/xlPivotAddIn.html

PC wrote:
Hit something weird today. We are making a lookup table to summarize a
lenghty list

EmpID
<no column
EmpBen EmpBenAtt

Layout is very important but just to give you an idea.

The concept we are after is they select the EmpID they want from the
drop down and it will give them a list of benefits for that employee.
Now since it is a long list and they're used to a text based system they
are typing the EmpID in. Most of the time this works well - but...

If they type something wrong they get a message "No item of this name
exists in the PivotTable report. Renmae 'X' to 'Y'?" And they hit OK.

At this point the PivotTable data in inaccurate. They've changed an
employee ID to something random and they don't remember what it is or
was (since we figure it out 3 days later).

I check the raw data that the pivot table is based upon and it's correct
- no changes made. So I do a refresh on the PivotTable and the messed
up data remains. I tried clearing Old items as oulined at
http://www.contextures.com/xlPivot04.html but that didn't make any
change.

So.. is there anyway to stop this behavior (protecting the sheet doesn't
work since it kills off needed functionality) OR how do I refresh the
data so that the correct data is displaying not the edited version?

I've only tried this in 2003 and have recreated with all data elements
in a PivotTable in 6 different spread sheets on 2 different computers
(way different models so I know it's not a image issue).


Hope that made sense...

Thanks,
PC_



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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
moving pivot table data source files Will C. Excel Discussion (Misc queries) 2 June 6th 06 12:48 AM
OLAP Pivot table - How to show items with no data ? Timmo Excel Worksheet Functions 1 March 30th 06 07:03 PM
Pivot table format changes if value of source data is null Frustrated with formatting! Excel Discussion (Misc queries) 0 October 24th 05 02:16 AM
Pivot Table Report formatting - can't select Data Source Order Becky Excel Discussion (Misc queries) 1 August 4th 05 07:33 PM
pivot table YingRui Oliviero Excel Discussion (Misc queries) 1 April 12th 05 01:57 PM


All times are GMT +1. The time now is 07:56 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"