Pivot Table - Numbers won't format correctly in row

**needexcelhelp23** · 12-15-2009, 12:31 PM

I have attached a very small example of the worksheet I'm currently working on to make my question easier. What I'm trying to do is show only the active accounts (denoted by a Y) listed in the "main" tab in a separate pivot table. I got the pivot table to work and it shows only active accounts; however, if I go into the "main" tab and switch cell D8 to a Y (denoting it active) and go back to the pivot tab and click refresh, although the new active account shows up, the number formating in cell D9 (54546) is incorrect. I want it to show up as a currency to match everything else and even though that entire column is formated as currency the number is showing up in just general formating. Do you know how to format the pivot table so that when new accounts become active and moved to the pivot table, the amount owed will be shown as a currency?

Also, is there a way to have the pivot table automatically refresh when you make a change rather than having to hit the refresh button every time?

Please help, I'm stuck at work!

Thank you!

**DonkeyOte** · 12-15-2009, 12:38 PM

Why is Total a Row Field and not a Data Field set to SUM ?

If you amend the field orientation as above you will find that you can apply your preferred Number Format to the Field itself.

**needexcelhelp23** · 12-15-2009, 12:56 PM

Great, that worked. Unless someone can help with the refresh question this thread is finished. How do I mark it as resolved?

**DonkeyOte** · 12-15-2009, 01:05 PM

Refresh - you would need to use VBA - do you want to go down that route ?

I note your seemingly running a Mac (?)...
if so it's worth mentioning perhaps that in 2008 there is no VBA (should you opt to upgrade down the line) and to be frank I don't really know what the VBA support is like for 2005...