|
VC Problem refreshing pivot table
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!" -- Vickster3659 |
Do you type in dates (01/21/2005 and format as MMM to get Jan)?
Or do you type in the text Jan, Feb, ...? Vickster3659 wrote: 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!" -- Vickster3659 -- Dave Peterson |
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. -- Vickster3659 "Dave Peterson" wrote: Do you type in dates (01/21/2005 and format as MMM to get Jan)? Or do you type in the text Jan, Feb, ...? Vickster3659 wrote: 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!" -- Vickster3659 -- 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? Vickster3659 wrote: 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. -- Vickster3659 "Dave Peterson" wrote: Do you type in dates (01/21/2005 and format as MMM to get Jan)? Or do you type in the text Jan, Feb, ...? Vickster3659 wrote: 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!" -- Vickster3659 -- Dave Peterson -- Dave Peterson |
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? -- Vickster3659 "Dave Peterson" wrote: 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? Vickster3659 wrote: 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. -- Vickster3659 "Dave Peterson" wrote: Do you type in dates (01/21/2005 and format as MMM to get Jan)? Or do you type in the text Jan, Feb, ...? Vickster3659 wrote: 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!" -- Vickster3659 -- Dave Peterson -- 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. Vickster3659 wrote: 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? -- Vickster3659 "Dave Peterson" wrote: 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? Vickster3659 wrote: 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. -- Vickster3659 "Dave Peterson" wrote: Do you type in dates (01/21/2005 and format as MMM to get Jan)? Or do you type in the text Jan, Feb, ...? Vickster3659 wrote: 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!" -- Vickster3659 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Thanks for your help! This worked for me....
-- Vickster3659 "Dave Peterson" wrote: 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. Vickster3659 wrote: 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? -- Vickster3659 "Dave Peterson" wrote: 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? Vickster3659 wrote: 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. -- Vickster3659 "Dave Peterson" wrote: Do you type in dates (01/21/2005 and format as MMM to get Jan)? Or do you type in the text Jan, Feb, ...? Vickster3659 wrote: 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!" -- Vickster3659 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 04:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com