Pivot table

[PatJennings]

In Col A is the Month (Jun,Jul,Aug) of transactions, listed in date sequence
in another column is the last name of the salesman for each transaction.
There are other columns relevant to the transactions, but not to the pivot
table I created. The resulting pivot table listed the salesman, but not in
alphabetical order. In the data columns are the counts of the transactions
for each month with a final column totaling the three monthly columns and a
grand total.

Now the date columns are arranged in this sequence: Jun Aug Jul. I cannot
figure out how to arrange the salesmen's names alphabetically, nor why the
date columns are not listed Jun, Jul Aug.

 

[Roger Govier]

Hi

Double click on the last name field>Advance>Sort>Ascending
Do the same for your Date field.
If the sort doesn't resolve the month order, then Tools>Options>List>Enter
Jan, Feb ..... Dec in white pane>Add
Then repeat Advanced sort on date field.

 

[PatJennings]

Thanks Roger, your solution worked.

If I may ask additional Questions:

I have two columns indicating the last name and state of the individuals.
The count of months are currently for Jun, Jul and Aug. There will be
additional transactions for Sep, etc.

When that data is entered, will I have to change the range of data, or will
it automatically include the new entries?

Can I create at the end of the table, or outside of it totals for the
monthly transactions by state? (It already gives me the totals for the
person's name by month.

Sincerely,
Pat

 

[Roger Govier]

Hi Pat

When that data is entered, will I have to change the range of data, or
will it automatically include the new entries?

The simple answer is No.
However, you can make the data source range grow automatically as you add
more data.
Depending upon your version of Excel, you may have an inbuilt method to
achieve this.
With XL2003 place cursor in your source data range>Data>List>Create List
With XL2007 again place cursor in data range>Insert tab>Table

In each case, if you then right click on the PT>Back>enter the name of the
table in the source dialogue =Table1, or whatever it is called, then the PT
will automatically include all new data when you Refresh the PT.

If your version is earlier than XL2003 (this also works with 2003 and 2007)
you can create a dynamic range.
Insert>name>define> Name myData Refers to
=$A$1:INDEX($1:$65536,COUNTA($A:$A),COUNTA($1:$1))

The above formula is a general formula that will work for all situations
assuming you have no entries in row 1 beyond the data you want to Pivot, and
that your header starts in A1.

If your data only went to say column H, then you could simplify the range to
something like
=$A$1:INDEX(H:H,COUNTA(A:A))
(I am assuming that column A will always be populated if there is a row of
data in the table.)

Again in the PT wizard, give source as =myData

Can I create at the end of the table, or outside of it totals for the
monthly transactions by state?

You can, but why not create them within the PT?
Drag State to the Row area, and place it above Name.
You will then have a count of Name, within State, and a Subtotal for the
State. If Subtotal is not showing, double click State on the PT and choose
Subtotal Automatic

 

[PatJennings]

Than you Roger, however, I am using 2002, and unfortunately I am having
difficulty creating the name for the data. Do I select the worksheet, or
the rows of existing data, or something else? Is relevant where the cursor
is when creating the name? My pivot tables are on separate worksheets.
Pat

 

[Roger Govier]

Hi Pat

Whilst on the sheet with the source data
Insert>Name>Define enter a name for the data range in the Name box > fill in
the formula given in the Refers to box