Pivot Table Problems

[itibot]

I need to create a PivotTable from an imported woksheet. I am having
two problems.

My Pivot Table is [Rows] Part Number [Columns] Month [Data] Order Qty

The purpose of the pivotTable is to Sum the order qty of said part
across each month. Sounds easy!

Problem 1.
The date field is imported into Excel from Access in the format dd-mmm-
yy i need it in the PivotTable in the format mmm-yy therefore i have
used the Year and Month functions (in spearate columns) to extract
that information and then Concatentate to pull them together in the
column i want. In my macro i have also attempted to format that column
but it doesn't seem to work.

Now when i pull the date into my PivotTable the format is General and
is sorted in alphabetical order not as a date.

Problem 2.
I also import a Order qty field as text, in excel i have attempted to
format it in a macro to number "0" format. When i put this into my
PivotTable it will not SUM the order quantities. It will count them
but when i sum them it returns a value of 0.

Please help! I am a novice with PivotTable's but the actual table is
very basic. I have tried to format the fields in the PivotTable but
nothing changes.

 

[Debra Dalgleish]

To convert the date and qty fields to real numbers, use one of the
techniques shown here:

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

Instead of three columns to pull the month and year and concatenate
them, you could use the TEXT function to format the data. For example,
if the date is in column A:
=TEXT(A2,"yyyy-mm")
Or, leave the date field as is, and use the Grouping feature in the
pivot table to group by year and month.

I need to create a PivotTable from an imported woksheet. I am having
two problems.

My Pivot Table is [Rows] Part Number [Columns] Month [Data] Order Qty

The purpose of the pivotTable is to Sum the order qty of said part
across each month. Sounds easy!

Problem 1.
The date field is imported into Excel from Access in the format dd-mmm-
yy i need it in the PivotTable in the format mmm-yy therefore i have
used the Year and Month functions (in spearate columns) to extract
that information and then Concatentate to pull them together in the
column i want. In my macro i have also attempted to format that column
but it doesn't seem to work.

Now when i pull the date into my PivotTable the format is General and
is sorted in alphabetical order not as a date.

Problem 2.
I also import a Order qty field as text, in excel i have attempted to
format it in a macro to number "0" format. When i put this into my
PivotTable it will not SUM the order quantities. It will count them
but when i sum them it returns a value of 0.

Please help! I am a novice with PivotTable's but the actual table is
very basic. I have tried to format the fields in the PivotTable but
nothing changes.

 

[itibot]

Thanks for the response,

I tried the TEXT function and it does change the format to mmm-yy as
you say, i also have formatted the column to mmm-yy and everything
looks rosy.
Next i put the data into the PivotTable and it still doesn't recognise
it as a date field (when i check in the PivotTable the format is
General) and is sorting it alphabetically ie. Apr-08,
Feb-08.......Oct-07, Nov-07.

I have already looked at the article on contextures and that doesn't
really apply to the issue i have here. Everything in the worksheet is
in the correct format as far as i want it but when i transfer it into
the PivotTable it doesn't recognise any of the formats. (I have
checked in the PivotTable options that Preserve Formatting is
checked).


What is wrong with it!??

Thanks in advance for your help!

To convert the date and qty fields to real numbers, use one of the
techniques shown here:

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

Instead of three columns to pull the month and year and concatenate
them, you could use the TEXT function to format the data. For example,
if the date is in column A:
=TEXT(A2,"yyyy-mm")
Or, leave the date field as is, and use the Grouping feature in the
pivot table to group by year and month.

I need to create a PivotTable from an imported woksheet. I am having
two problems.

My Pivot Table is [Rows] Part Number [Columns] Month [Data] Order Qty

The purpose of the pivotTable is to Sum the order qty of said part
across each month. Sounds easy!

Problem 1.
The date field is imported into Excel from Access in the format dd-mmm-
yy i need it in the PivotTable in the format mmm-yy therefore i have
used the Year and Month functions (in spearate columns) to extract
that information and then Concatentate to pull them together in the
column i want. In my macro i have also attempted to format that column
but it doesn't seem to work.

Now when i pull the date into my PivotTable the format is General and
is sorted in alphabetical order not as a date.

Problem 2.
I also import a Order qty field as text, in excel i have attempted to
format it in a macro to number "0" format. When i put this into my
PivotTable it will not SUM the order quantities. It will count them
but when i sum them it returns a value of 0.

Please help! I am a novice with PivotTable's but the actual table is
very basic. I have tried to format the fields in the PivotTable but
nothing changes.

 

[Debra Dalgleish]

I suggested formatting it as yyyy-mm, not mmm-yy
With the yyyy-mm format it would sort by year and month.

Did you try any of the suggestions for converting text to numbers?
It doesn't matter what format the cell shows. If the contents are not
real numbers, they'll sum as zero in the pivot table.

Thanks for the response,

I tried the TEXT function and it does change the format to mmm-yy as
you say, i also have formatted the column to mmm-yy and everything
looks rosy.
Next i put the data into the PivotTable and it still doesn't recognise
it as a date field (when i check in the PivotTable the format is
General) and is sorting it alphabetically ie. Apr-08,
Feb-08.......Oct-07, Nov-07.

I have already looked at the article on contextures and that doesn't
really apply to the issue i have here. Everything in the worksheet is
in the correct format as far as i want it but when i transfer it into
the PivotTable it doesn't recognise any of the formats. (I have
checked in the PivotTable options that Preserve Formatting is
checked).


What is wrong with it!??

Thanks in advance for your help!

To convert the date and qty fields to real numbers, use one of the
techniques shown here:

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

Instead of three columns to pull the month and year and concatenate
them, you could use the TEXT function to format the data. For example,
if the date is in column A:
=TEXT(A2,"yyyy-mm")
Or, leave the date field as is, and use the Grouping feature in the
pivot table to group by year and month.

I need to create a PivotTable from an imported woksheet. I am having
two problems.

My Pivot Table is [Rows] Part Number [Columns] Month [Data] Order Qty

The purpose of the pivotTable is to Sum the order qty of said part
across each month. Sounds easy!

Problem 1.
The date field is imported into Excel from Access in the format dd-mmm-
yy i need it in the PivotTable in the format mmm-yy therefore i have
used the Year and Month functions (in spearate columns) to extract
that information and then Concatentate to pull them together in the
column i want. In my macro i have also attempted to format that column
but it doesn't seem to work.

Now when i pull the date into my PivotTable the format is General and
is sorted in alphabetical order not as a date.

Problem 2.
I also import a Order qty field as text, in excel i have attempted to
format it in a macro to number "0" format. When i put this into my
PivotTable it will not SUM the order quantities. It will count them
but when i sum them it returns a value of 0.

Please help! I am a novice with PivotTable's but the actual table is
very basic. I have tried to format the fields in the PivotTable but
nothing changes.