pivot table 2000 -> 2002 upgrade error

[Runyan]

I have recently upgraded to 2002 from 2000. I have a Pivot table that
I use to create reports from my company's time accounting system. I
used a saved web query to pull an html table into the spreadsheet, and
then refresh the data. I do this every week when time is updated.
The pivot table summs the data nicely based on client, project, you
get the idea.

The format of the time value cell (the hours that people work) has
always been General. It still is. However, now in 2002, the pivot
table does not read the number as a value that it can sum. My pivot
table only display zeros. I have tried formatting the cells in the
data to numbers, and formatting the cells in the Pivot Table to
numbers. Doesn't fix the problem.

What I have found that fixes the problem is to manually re-type the
data, after it is imported. Let's say I have a column of:

8
9
11
3
4

All I have to do is re-type those values in the spreadsheet over the
existing ones (do not need to adjust the format) and the pivot table
will read the values. The only problem is, I have over 2000 rows of
data and cannot re-type all of them! Why doesn't it read the values
when imported, but does when I type them in?

Any ideas why this is happening and what I can do to fix. I am at my
wits end!

Thanks,
Brian

 

[Debra Dalgleish]

I'm not sure why it's happening, but to change the data to numbers:

1. Select a blank cell, and copy it
2. Select the cells that contain the time values
3. Choose Edit>Paste Special
4. Choose Add, click OK.

 

[Runyan]

Debra,

Thanks! It worked. What did that funtion do? Just refresh the cells?
Much appreciated.

Brian