VC Problem refreshing pivot table

[Guest]

Hi,
I have a worksheet that gets new data added once per month, using "Month" as
a column. Before I added May data, my pivot table report displayed in the
correct order- Jan, Feb, Mar, Apr, Grand Total. I'm using Office XP.

After adding May data and refreshing the pivot, my pivot table now is
displayed as Jan, Feb, Mar, May, Apr, Grand Total. How do I get May to be
displayed after Apr? The data is pasted into the source worksheet with May
after April.

This does not happen all the time, but is extremely frustrating when it does
because I have to tell the recipient "I don't know why this happens!"

 

[Dave Peterson]

Do you type in dates (01/21/2005 and format as MMM to get Jan)?

Or do you type in the text Jan, Feb, ...?

 

[Guest]

Hi Dave,
Actually, this data comes from an Access database, from which the field
Month is an update query entering text into this field. Once the query is
run, it gets exported to Excel, then a 'copy/paste' into the spreadsheet,
then the pivot is refreshed. I even tried to reformat the column in Excel to
"M" but that didn't work either.

 

[Dave Peterson]

Can you use a helper column to convert it to dates?

=DATEVALUE(A1 &" 1, 2005")

Then format that cell the way you like and use it in your pivottable?

Hi Dave,
Actually, this data comes from an Access database, from which the field
Month is an update query entering text into this field. Once the query is
run, it gets exported to Excel, then a 'copy/paste' into the spreadsheet,
then the pivot is refreshed. I even tried to reformat the column in Excel to
"M" but that didn't work either.

 

[Guest]

sorry....In the grand scheme of my company, I feel I'm a pretty saavy user,
but compared to you guys, I will always be a rookie.... can you elaborate
please? would this be another column in the database or source spreadsheet?

 

[Dave Peterson]

Since I don't know anything about Access, I would use a column in excel.

But my general feeling is to correct the data at the source--not in lots of
programs/reports that use the same data.

I'd see if the Access people could do something for me. Either add a field that
converts the entry to a real date or make the users enter the field as a date.

sorry....In the grand scheme of my company, I feel I'm a pretty saavy user,
but compared to you guys, I will always be a rookie.... can you elaborate
please? would this be another column in the database or source spreadsheet?

 

[Guest]

Thanks for your help! This worked for me....