I am updating a pivot table with data from a worksheet. All the fields
update correctly except the "spend" field which contains numbers - the field
I am updating displays "0.00" even though there are numbers in the original
worksheet where I collected the data from. The data collected appears in the
wrong field so the overall total is correct but it is appearing in the wrong
field - does this make sense to anyone? Can anyone help?

Can you display several rows of your original data file? Sanitize it if
necessary.

"CatherineC" wrote:

I am updating a pivot table with data from a worksheet. All the fields
update correctly except the "spend" field which contains numbers - the field
I am updating displays "0.00" even though there are numbers in the original
worksheet where I collected the data from. The data collected appears in the
wrong field so the overall total is correct but it is appearing in the wrong
field - does this make sense to anyone? Can anyone help?

Hi Barb,

Thanks so much for your response. I have since realised that my problem is
that the numbers in the original source are not recognised by my current
worksheet - and when I try format the cells as either numbers or custom
format it doesnt accept that so I've had to manually change the numbers which
is a lengthy process....so now I've figured out why it won't update the pivot
table, now I need to know how I change the cells to a number automatically
without having to manually change them....

"Barb Reinhardt" wrote:

Can you display several rows of your original data file? Sanitize it if
necessary.

"CatherineC" wrote:

I am updating a pivot table with data from a worksheet. All the fields
update correctly except the "spend" field which contains numbers - the field
I am updating displays "0.00" even though there are numbers in the original
worksheet where I collected the data from. The data collected appears in the
wrong field so the overall total is correct but it is appearing in the wrong
field - does this make sense to anyone? Can anyone help?

Hi Catherine

To convert the text numbers to numeric, you could try entering a 1 into
a blank cell which is formatted General.
Copy that cell.
Mark the range of your data with the text numeric's and Paste
SpecialMultiply.
That sometimes works.

Or, assuming your data is in column A, in another column could enter
=--A1 and copy down.
If this works, then copy the block of new cells and Paste SpecialValues
over the top of your original data. You can then delete the extra column
you created.

If neither of those methods work for you, it could be that the data
contains the non-breaking space Char(160). This often happens if the
data has been copied from the web. In which case, David McRitchie has a
routine called TRIMALL at his website which may help you.
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

--
Regards

Roger Govier


"CatherineC" wrote in message
...
Hi Barb,

Thanks so much for your response. I have since realised that my
problem is
that the numbers in the original source are not recognised by my
current
worksheet - and when I try format the cells as either numbers or
custom
format it doesnt accept that so I've had to manually change the
numbers which
is a lengthy process....so now I've figured out why it won't update
the pivot
table, now I need to know how I change the cells to a number
automatically
without having to manually change them....

"Barb Reinhardt" wrote:

Can you display several rows of your original data file? Sanitize it
if
necessary.

"CatherineC" wrote:

I am updating a pivot table with data from a worksheet. All the
fields
update correctly except the "spend" field which contains numbers -
the field
I am updating displays "0.00" even though there are numbers in the
original
worksheet where I collected the data from. The data collected
appears in the
wrong field so the overall total is correct but it is appearing in
the wrong
field - does this make sense to anyone? Can anyone help?