Excel 2007 : Pivot table refuses to preserve formatting even when instructed to...

Having trouble with Excel 2007 and pivot table format retention.

I have selected the checkbox in pivot table options to tell it to 'preserve cell formatting on update', but it simply won't, hehe. I have columns of data (not in the 'values' section, but in the 'row labels' section) that are formatted to 'currency' or 'date' or 'general' (text); I can highlight the entire column and choose to 'center' the text and it will do so, but upon refreshing the table, everything reverts to right-justified.

If I pick one single cell and center it then refresh, however, it will stay centered. More than one cell at a time will fail, and none will stay centered.

Also, formatting the date column to show mm/dd/yy works, but upon refreshing it reverts to m/d/yyyy, even though the source data is mm/dd/yy! And unlike other fields, the 'field settings' for this particular field does *not* show the 'number format' button (lower left of pop-up window) when chosen. Good grief.

There seems to be no rhyme or reason as to whether it will or will not preserve formatting - what the heck? Any ideas as to how to convince it to actually do this?

EDIT: Never mind, solved it myself; 'enable selection' was the key.

Please elaborate! I'm banging my head on the desk right now over this exact issue. It's been a couple hours now and I'm irate! Why does Excel refuse to let the format be? What do you mean "enable selection"? How did you use it programatically? BTW I'm using web queries and VBA but reading around the web it seems to be the same problem as with pivot tables.

Thanks,
Jacob

This is a 3 yr old thread and you are not likely to get responses. Why not create your own thread? You will get faster replies.

http://support.microsoft.com/kb/162028

Microsoft acknowledgement and response