External Data connection in Pivot turns one column of numbers into text but not others ?

[jodijan]

I have a pivot which uses another external spreadsheet , connected via "use an external data source"

The data source has two columns , one for "Actual Spend" , one for "Budgeted Spend" . Both columns in the source file are formatted as Currency format and display as currency with no problems.

In the pivot table , again both Value fields are set to be currency but here's the problem !...

The "Actual Spend" values display as currency values correctly in the Pivot table but the "Budgeted Spend " values , when refreshed turn to zero . When I drill down on a "Budgeted Spend" value I can see the Currency format has been changed to a text format and therefore does not show on the Pivot table , however...

If I open the External data source spreadsheet independently alongside the pivot spreadsheet and refresh the pivot , the values turn back to currency format in the pivot ! . But if I then close the external data source spreadsheet and refresh the pivot data they "zero" again being reformatted to text.

I have tried creating different versions of the pivot SS but the problem reoccurs . It also makes no difference if the "save source data with file" is checked or unchecked in pivot table options .

Any help much appreciated

Thanks

John