pivot table problems

[CatherineC]

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?

[Barb Reinhardt]

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?

[CatherineC]

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?

[Roger Govier]

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
Special>Multiply.
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 Special>Values
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" <[email protected]> wrote in message
news:[email protected]...
> 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?