Pivot table problems

[Neil G]

Hi

I created a pivot table from several sheets, using the multiple
consolidation option.

My data is all the same with headings:
Name Nationality 1936 (Note this is points scored, but the pivot will
show points for the year 1936, 37 etc, then sum them)
Fred England 15
etc
etc

The pivot shows
Page1 All

Sum of Value Column
Row 1936 1937 Nationality Grand Total
Fred 15 6 0 21
etc

So, I really want the Nationality to show 'England' and not 0 and be
alongside riders name

In the Pivot Table Field List picker all I get is
Row
Column
Value
Page1

I can't figure out (if indeed I can!) how to move the Nationality.

Ideally I'd like to be able to group the Rows by Nation at some point

Thanks for helping

Neil

 

[Debra Dalgleish]

When you create a PivotTable from multiple consolidation ranges, you
don't get the same pivot table layout that you would from a single range.

If you move Nationality, so it's the first column in your data source,
the row heading should show the nationalities. The column area will show
the years and Name. You can change the function that's being used by the
data value, but it will use the same function on all these columns.

After you've created the Pivot Table, open the dropdown list for Column
headings, and hide the Name column, which will be full of zeroes.

If possible, store the data all on one sheet, or in a database, such as
Access, and use that as the source for the PivotTable.

 

[Neil G]

Debra (or anyone)

I'm having real trouble with my PivotTable!

I tried, as suggested, creating a database (Access 2002/XP) with data stored
thus in a table:

Rider Number, Name, Country, Points, Year (Headings)
1, Arthur, England, 8, 1936
..
..
18 Bob , USA, 5, 1936 (I.E. Riders 1 through 18 for 1936)
1, Joe, Australia, 2, 1937
..
..
18, Bob, USA, 8, 1937 (as above 1-18 for 1937)

and so on...
So, to summarise that, there is a unique line for each competitor for each
year

Back to Excel to create a PivotTable, I opened the DB as the source fine,
and dragged the boxes around trying to get my layout to work like this:

Sum of points Year
Name, Country, 1936, 1937 etc etc Grand Total

The data I am getting is
Arthur England 0 0
Arthur Total 0 0
Bill England 0 0

Instead of the points he scored
And... if I click on less data, the country dissappears.

Help please!!!

Thanks
Neil

 

[Debra Dalgleish]

In the database, is Number the data type for the field named Points?

 

[Ed Ferrero]

Hi Neil,

Check the Table design in your Access db. Is 'Points' a text field? If so,
change to numeric.

Also, look at http://edferrero.m6.net/Pivot.html for help on building a
Pivot Table from an Access database.

Ed Ferrero

 

[Graham Speers]

Thanks Ed,

It was a text field, as I had wanted to include text when a rider was a
reserve but didn't compete, I have a code 'DNR' (Did not ride)

So, almost there, I still have a problem showing the Nationality, as when I
use 'Hide Detail' it hides that column!

Hmm, will persevere, but if anyone knows why, would appreciate some help
still

Thanks to Debra too

Neil

 

[Debra Dalgleish]

Why are you choosing 'Hide Detail'?

Thanks Ed,

It was a text field, as I had wanted to include text when a rider was a
reserve but didn't compete, I have a code 'DNR' (Did not ride)

So, almost there, I still have a problem showing the Nationality, as when I
use 'Hide Detail' it hides that column!

Hmm, will persevere, but if anyone knows why, would appreciate some help
still

Thanks to Debra too

Neil

 

[Neil G]

Because ever other line has
"the previous lines name" sum

IE
Arthur England
Arthur Sum
etc

Neil

 

[Debra Dalgleish]

To get rid of those subtotals:

Double-click on the 'Name' field button
Under Subtotals, choose None
Click OK

 

[Neil G]

....and I just figured it out.

All looking great now

Thanks all