Pivot table treating numbers as text in value field

[Rune Wentzel Helms]

I have a sheet that sums up costs for clients, by adding them through an If
function, so identical clients are added up in a single cell. This coloumn is
formattet as numbers.

When I import the sheet to a pivot table in another sheet, it treats the
numbers as text (I believe). I can see the numbers under the price field, but
in the table it is shown as "0" or "division by 0" depending on the setting
in the value field.

I have tried copy pasting all the data to a sheet in the same file as the
pivot (inserting values) - and this works fine. I have also consulted our
"in-house" specialists, but they have never seen this problem before - also
it worked without problems initialy, but after an update all prices
dissapeared.

I hope someone can help, as it would take quite a long time to recreate the
sheets from scratch.

Sincerely Rune Helms

(I use a danish version of excel, so the terms might be a bit off, as I have
just translated to english. If anything is unclear just ask)

 

[Sean Timmons]

I would assume the sheet you are pasting into is not allowing the numbers to
be formatted as number. Try going into a blank cell in the sheet and typing
1.

Copy the 1, highlight your cost column, and paste special/multply.

then, refresh your pivot. Does it see them as numbers now?

 

[Rune Wentzel Helms]

I already tried multiplying the cost column by one into a new column - that
works fine in the sheet, but doesn't change the problem in the pivot.

If I copy paste all the data to a new sheet as values, there is no problems
in getting a pivot to read the values - however I need the "If" function to
ensure that data is updated when new clients are added - or new costs are
added to an existing client.

- Rune Helms


"Sean Timmons" skrev: