pivot table 2000 -> 2002 upgrade error

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
0
runyan32 (5)
9/15/2003 8:52:52 PM
excel 39879 articles. 2 followers. Follow

2 Replies
517 Views

Similar Articles

[PageSpeed] 19

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 wrote:
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd (439)
9/15/2003 9:57:07 PM
Debra,

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

Brian
0
runyan32 (5)
9/17/2003 10:18:53 PM
Reply:

Similar Artilces:

Take long time to load Outlook 2002 approx. 60 seconds
I tried to update the latest windows service pack 1 updates in order to help me to fix any errors related but still the same problem. The loading time taken for Microsoft Outlook takes approx. a minute, even though, my inbox is empty. However, most of the usage is for the contacts only. HELP! ...

how do I get publisher 2000 to print avery labels
In Microsoft Publisher 2000, how can I print to Avery label format? File, Page setup, tick labels, the selections are old but there should be an equivalent to your size label. If you are unsure, go to www.avery.com type your Avery number in the search on the left, the search will come back with compatible sizes. For example 5160 is also Avery 15160 15660 18160 18660 5160 5260 5520 5630 5660 5960 8160 8250 8460 -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "KJ Mike" <KJ Mike@discussions.microsoft.com>...

SMRTINET.INI error
I get this error about every third time I do the internet updates in Money 2004. "Money encountered an error processing the dowloaded file ('SMRTINET.INI')." Yes the error log mispelled the word downloaded! Anybody have any idea? In microsoft.public.money, Stephen wrote: >I get this error about every third time I do the internet >updates in Money 2004. "Money encountered an error >processing the dowloaded file ('SMRTINET.INI')." Yes the >error log mispelled the word downloaded! > >Anybody have any idea? Close Money In Inter...

fatal error LNK1104
I get the following error while trying to link the sqlite.lib Linking... LINK : fatal error LNK1104: cannot open file "sqlib3.lib" Error executing link.exe. Before that, I did the following things: Project->Settings->link tab : Object/library modules: sqlib3.lib Tools->Options->Directories tab and added the folder on where sqlib3.lib resides I've also copy the sqlib3.dll to Window/System32 Why do I get that error? Thanks for ypur time. Ok, i've solved the problem Martin wrote: > I get the following error while trying to link the sqlite.lib > > Li...

Testing for an error in a range of cells...
I searched through the previous posts on this topic, but have almost no experience using VBA in excel, so I was wondering if there was a simpler way to achieve this: I want to write a formula in B2 that will basically say, "If the range of cells B3 to B24 contains an error, return x if true, and return y if false." I know how to do this for a specific cell rather than a range, for example (=IF(ISERROR(B2),"x","y"). Any help in achieving this without delving into VBA would be much appreciated (I just don't have the time to start learning this stuff as I am w...

Outlook has encountered a fatal error and must close
When composing messages I get the above message. the program closes and I am never sure whether my message has been lost. This message and abrupt shut down only started happening in the last month. Sending the error report has yielded nothing despite sending the error report every second time. Re: Terms of use below: I would just like it fixed!!!! Did you recently install a newer version of Ad-Aware? -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. ALWAYS post your Outlook version. How to ask a question: http://support.microsoft....

Adding and subtracting hours in Excel 2000 worksheet
I'm having some success with the addition part but not with the subtracting part! I have an Excel spreadsheet that is used to keep track of time worked by staff on a weekly basis and I need to keep track of the overtime worked. For example, one staff member has worked 40 hours, 54 minutes and 25 seconds in a week (displayed in a cell as 40:54:25 with the cell formatted as Category: --> Time and Type:--> 37:30:55). I then went to subtract 40 hours from the total hours worked to get the overtime displayed and thats where I'm having the problem. I tried the format of "=F9 -...

Unwanted Money 2002 install
I keep getting a Money 2002 install utility popping up....and I can't get it to close or stop trying to install! Whenever I hit "cancel" it just pops right back. I suspect I've picked up some virus or trojan, but I've run anti-virus (McAfee) and spyware (Microsoft Antispyware) software with the latest updates, but nothing seems to be catching it. I've also downloaded the latest Windows updates. I'd appreciate ANY ideas on how I could get this to stop! PLEASE email me at heresharry(removethis)@juno.com. THANK YOU! ...

Categorizer?
Hi all, i am receiving this situation with Exchage2003 (SBS) Email is downloaded with pop3 connector and then located in internal mailboxes Some mailboxes are not receiving email, other without problems. I do message tracking on these lost messages and i see that right after the message been passed to the categorizer the flow ends up there. I check event viewer and i have error code 1070 talking about not an addresses matching the internal client. i have checked this and the external address is defined in the default recipient policy and has been applied to these internal clients. any cl...

CRM 3.0 Installation help...2 errors
Going through the system requirements part of the setup I get a Warning on the IIS part. It cant access the URL-- Error accessing URL http://IRONWOOD-CRM: The remote server returned an error: (500) Internal Server Error. The second error I get is : Setup failed to validate specified Reporting Services Report Server http://ironwood-crm/ReportServer. Error: Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'. Chris Holub MCP CCNA 1. Check your IIS and default CRM virtual directory is there issue accecing, can you send me detail or s...

Error in saving quote
Hi, when I want to save quote, there is error message like this : <details>Server was unable to process request.</details> Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Runtime.InteropServices.COMException: <details>Server was unable to process request.</details> Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding t...

Error: "The name could not be resolved. The bookmark is not valid"
I am getting the error as described in the title when I attempt to connect to and my exchange server. 1) Setup One Exchange 2003 organisation (with a back end server in parent domain and one in child domain). Exchange 2003 Forest. A few Exchange 2003 front end servers in DMZ Office 2000 and Office 2003 on Windows 2000 Windows XP clients. ssl OWA and MAPI access (planning to investigate httpsRPC). 2) Problem When I connect with MAPI Outlook clients to child domain Exchange 2003. It comes up with the error: ...

Two Inboxes in Outlook 2000
Something weird happened recently and as a result I now have two complete sets of folders containing calendar, contacts, inbox, etc. One set is under "Archive Folders" and the other set is under "Personal Folders". All of the desired folders (calendar, contacts, inbox, personal folders, etc.) are under "Personal Folders", but all of my incoming mail arrives in the undesired inbox under "Archive Folders". I've tried to close the Archive folder set but a message tells me I can not do that. Does anyone know how to get rid of the undesired set of fol...

Outlook 2002 will not send and recieve on created account
Outlook 2002 began acting up and it lost one of my e-mail accounts. I noticed that I was not getting any e-mail that I would usually get with the account. I checked and the account is not there. The other thing that I have noticed is that my address book and archives are listed where my other accounts are listed in the area where you would edit or add e-mail accounts. I went in and recreated the missing account and went to send and recieve and it would not bring up the account in the send and recieve box (it brought up all my other accounts). I went back and the account is still...

error with exchange 2003
since last week, the application log is full of following message: the service threw an unexpected exception which was caught at f:\titanium\src\lra\abv_dg\serveragent.cpp(4511) this is domain controller (win 2003 server) and exchange server (exchange 2003. the network is running with 8 local computers. i could not find any help related to this error message and i need some help with this issue. thanks Any help from http://groups.google.com/groups?as_epq=service%20threw%20an%20unexpected%20exception%20which%20was%20caught&safe=images&ie=ISO-8859-1&as_ugroup=microsoft.publi...

Page icons in Publisher 2000 have disappeared.
My page number icons have disappeared. Now I have to click on "go to page __" to move from page to page. How do I get those icons back? View, Toolbars, Status bar... -- Mary Sauer http://msauer.mvps.org/ "GabPub" <GabPub@discussions.microsoft.com> wrote in message news:ED718A66-1F8C-4F59-93DA-71FC7B4CE2E8@microsoft.com... > My page number icons have disappeared. Now I have to click on "go to page __" > to move from page to page. How do I get those icons back? Select View, Tool Bars, Status Bar. That should bring back the page icons. -- Do...

Rounding errors in manually entered investment account transaction
I am tracking the performance of the investment subaccount of my life insurance policy in MS Money 2006 Premium. My insurer does not support online updates of investment subaccount performance, so I must enter statement data manually. Shares of investments are only reported with 3 decimal places of accuracy on my statements. As a result, in some cases rounding errors in the number of shares purchased and sold accumulate over months so that after a while the total number of shares that I own as reported by Money differs from the number of shares reported by my insurer. How do I adju...

Error Message 08-17-10
Good Morning, I am trying to make Windows Mail my new mail account. This is the message that keeps popping up. Does anyone have any idea on how I can get connected? Thanks in Advance, Kasey Account: 'Home', Server: 'pop3.zoominternet.net', Protocol: POP3, Server Response: '-ERR authorization first', Port: 110, Secure(SSL): No, Server Error: 0x800CCC90, Error Number: 0x800CCC18 yes i am getting same one and just got off of chat with comcast for 1 hr. and still not resolved "Kasey Jennings" wrote: > Good Morning, > I am trying...

Can't Send from Outlook 2002
I am using Hotmail, and can receive all folders, but can not send anything! The email moves from the "outbox" to the "sent mail" but never goes anywhere. Anyone know how to fix this? Tks ...

error 0X80070057 #3
I cant setup this mail service program. error 0x80070057 -- Thank You It's a long shot but see if the resolution in this article helps: http://support.microsoft.com/default.aspx?scid=kb;en-us;326842 "onelot" <onelot@discussions.microsoft.com> wrote in message news:F031D2BA-12A9-40A9-AFB8-814CF03B967F@microsoft.com... >I cant setup this mail service program. error 0x80070057 ...

Error reassigning leads following upgrade to version 4
Hi, We are experiencing an issue when trying to reassign a lead to another user. Whenever we do we get the following error : CrmException: More than one user queue was found for the given object.] at Microsoft.Crm.CrmException.Assert(Boolean condition, String message) at Microsoft.Crm.ObjectModel.QueueServiceInternal`1.GetUserQueueWithObject(BusinessEntityMoniker moniker, Guid userId, ExecutionContext context) at Microsoft.Crm.ObjectModel.QueueItemService.RouteToUser(BusinessEntityMoniker objectMoniker, Guid userIdOfCurrentObjectOwner, Guid primaryUserIdOfQueueToRouteTo, Execut...

Error message when trying to use my access add-in with VS2008
I created a Access 2007 add-in with VS2008 C# and I'm trying to debug my access form that sends text to a textbox to be displayed after I click on a button. I can step into 'FillTextButton_Click()' in my C# app that gets called when I click on the access form button. So I have my button and textbox connected to Access2007 with the Access onLoad() event. Everything seems to be connected ok. But as soon as I finish running through the 'FillTextButton_Click()' I get a error message saying "The expression On Click u=you entered as the event property se...

Error message with CAPICOM 2.1.0.2 Not enough storage is available
Hi everybody, I need some help. With CAPICOM 2.1.0.1, when I try to store certificates from a smart card I use Store := CoStore.Create; Cert:=CreateComObject(CLASS_Certificate) as ICertificate2; Store.Open(CAPICOM_SMART_CARD_USER_STORE, 'MY',CAPICOM_STORE_OPEN_READ_ONLY); without any problem. I have updated with CAPICOM 2.1.0.2, and the same code produces an error message:"Not enough storage is available to complete this operation" when store.open. I need use this library. My os is Microsoft Windows XP Professional Version 2002. SP3. My Internet e...

Exchange Calendar update tool = unable to find mailbox timezone:Error 0x80004005
When i run the exchange calendar update tool i rececive the following message "Unable find mailbox timezone:Error 0x80004005". I have given my user account full permissions to each exchange store. What else could be causing this problem? alos how do i grant exchange view only admin, exchange full admin, and send as rights to my user account manullay. This script is not working for me? thanks "Jef A" <jef.aldrich@tatumllc.com> wrote in message news:%23AssN4YYHHA.4008@TK2MSFTNGP05.phx.gbl... > When i run the exchange calendar update tool i rececive the follow...

Money 2007 Deluxe OFX import error on only one account at bank
I have multiple accounts at R1CU and have them all set up for web imports. Today, I added two additional identical-type accounts that have been existing for a month at the R1CU website. The first account, let's call #14, imported transactions just fine. The second account, #15, will not bring anything in. It won't even search for the new account on import or ask me to which account the import should post. It just gives me the "import complete" window and transfers me to my list of web-enabled accounts and their download history. There, the #15 account isn't even lis...