Share this content
0
9
1636

Pivot Table help!

Pivot Table help!
Pivot Table help!
I have created a pivot Table (PT) from a data. The data consists of lots of columns (Fields). One of the fields is Customer Names. The Customer name was created using a data validation list. If I enter a new customer name which is not in the original data list, I would expect the value of that particular customer will not pick as Sum Value even if I refresh the PT. However, I believe I removed the data list by going in to Data validation and clear it. I have also checked the name of the list by going to Names (Names Manager) to see if the list name is still there. Now, when I enter a new customer name, PT still does not pick the new name, but if I change the new name in the old one, PT picks the value. I have rechecked the range by going through the source of the data, but I still could not figure out why a new customer name is not in the PT report.
Please help.
 
Anon

Replies

Please login or register to join the discussion.

By shurst
08th Apr 2010 20:14

Some clarification

Hi - could you just add a bit more about the following part of your description of the problem:

"Now, when I enter a new customer name, PT still does not pick the new name, but if I change the new name in the old one, PT picks the value."

I'm not sure I fully understand what you mean by 'if I change the new name in the old one' - what do you mean by old one? Also, when you say that you" enter a new customer name and the PivotTable does not pick the new name", whereabouts are you entering the new name?

 

Thanks (0)
avatar
09th Apr 2010 08:00

Check that the PT

is looking at the named range and not deciding for itself (Data/Pivot Table/PT Wizard/ Back button).

Thanks (0)
avatar
By neileg
09th Apr 2010 09:26

A tip

When you build your pivot it will tend to select a fixed block of data, A$1$:Z$99$ for example. If this is a list you will be adding to, then give the data range as colums only, e.g. A$:Z$

Thanks (0)
avatar
By Anonymous
09th Apr 2010 10:17

Pivot Table Help

 

Thanks all for your suggestions.Simon:I have list of customer names on Column “A”. These names are existing names which are created through a data validation list. Let’s say, I have lists of customer names, Smith, John and Bill.When I added a new customer name (e.g., Dan), which is not in the original list, PT does not update my data. Dan and sum value do not appear in the PT report. However, if I were to change the new name back in to one of the existing ones, it will. In other words, if I add a new name in to source data with “Dan”, PT will not show this in the report, but if I change “Dan” to one of the existing customers, John, Bill, or Smith, PT updates the data. Therefore, Pivot is not recognising Dan’s and his sum value. Why?

Thanks (0)
avatar
09th Apr 2010 11:08

Have you got...

...."Show all" ticked?

Thanks (0)
By shurst
09th Apr 2010 11:30

Pivot table - customer name 'Show all'

I agree with Paul, check this first. Am I correct in understanding that you have also double-checked that you pivot table is linked to the correct data range?

Thanks (0)
avatar
By Anonymous
09th Apr 2010 12:36

PT

Paul and Simon: Thank for your help!

It may sound stupid,but I am struggling to locate the "All Show" Menu, or button. I have looked at the Pivot Table options and can see the Show/Hide.

 

Thanks (0)
avatar
09th Apr 2010 13:01

Show all

At the top of the column containing the names in the pivot table, there will be a field name heading and a drop down box. Open up the drop down list by clicking on the down arrow and check that "Show all" is ticked.

Thanks (0)
avatar
By Anonymous
09th Apr 2010 13:59

PT

HI Paul,

It now works.

Thanks very much!

You saved me lost of time and pain.

Annon

Thanks (0)