Excel problem on 1 machine out of five

  Bandy 11:05 27 Jun 2006

I have a problem with Excel Pivot Tables on one machine but not on the others that are networked to it.

The Pivot tables show Job Numbers costed by Date, the problem is that sometimes I can select the appropriate dates from the drop down list and sometimes not.

There seems to be no rhyme or reason why, or when, this happens and the same sheets work perfectly every time on the other machines.

I'm using Excel 2000 and have tried re-installing it on the offending machine only to have the problem re-occur a few hours later.

The problem first started after the last major Windows update this month so tried uninstalling them, with no success, then tried re-installing windows and excel but still the problem has come back. I didn't think the updates were the cause but thought it worth a try just in case

This problem occurs with any spreadsheet on this single computer. The spreadsheet works perfectly except that the dates cannot be selected on the pivot table.

Any clues towards a solution would be most appreciated

  silverous 11:31 27 Jun 2006

When you say you cannot select them..what do you mean? They aren't there? OR they are 'greyed out' ?

If you have rebuilt windows entirely and excel then it sounds like it could be hardware although I can't see why that would cause this only.

Certain that the same updates and office version and patches have been applied to all?

  Bandy 12:05 27 Jun 2006

Thanks for the response

The dates are all there, and visible, they are not greyed out and when selecting those dates from the drop down list the appropriate date is highlighted in blue, but a tick does not appear in, or disappear from the box

The appearance of the sheet is exactly the same on the "awkward" computer as the other with the exception that the drop down boxes cannot be selected or deselectd on that machine alone.

Yes I've ensured that all computers are at the same stage with all updates.

I too am coming around to some hardware fault but cannot think what it can be. The variable nature of the fault is a real problem I can't get my head around at the moment

  silverous 12:53 27 Jun 2006

Lots of searching around MS knowledgebase etc. and can't see anything like this...can't believe you are first to find this 'bug' (although someone has to be!)...if it is a bug that is.

Is it confidential or would you be able to send me a copy? I'd be interested to take a look at it to see if I can 'see' anything wrong.

  Bandy 13:39 27 Jun 2006

I know the feeling about not believing, and searching for the problem

Thank you for the offer. I'll email you although I'll need to change some of the details

  silverous 15:19 27 Jun 2006

..got the spreadsheet via email.

2 things spring to mind.

i) 4MB is large considering the amount of data you have in it.

ii) The chart sheet didn't seem to work.

RE: i) I note that the pivot table source is set to be the entire spreadsheet from A to AQ (all 65536 rows). When I reduced this to be A1:AQ30000 the size of the spreadsheet dropped to be 1.7MB (i.e. more than halved). Do you need it to cope with that much data i.e. will it ever contain 65536 rows?

It might not be the problem, I just wonder if that particular machine is having trouble with processing 65536 rows x "up to Q" columns of data in a pivot. Does that machine have any less memory than the others?

One thing I wasn't clear on - is it consistently doing this or just every few uses?

Might be worth dropping the pivot table source range down to say 10,000 rows and trying that for a while.

  Bandy 15:57 27 Jun 2006

Thank you silverous

You are correct about the size and I will amend as suggested.

The fault, if it can be called that, appears after no set time but once it appears I can't seem to correct it. Than again after no set period it starts working again.
The computer that is having the problem is a laptop with 512Mb memory, the others have either 768Mb or 1024Mb. Although having said that, when that particular laptop is replaced with another of exactly the same make and model, and with the same amount of memory, 512Mb, then the sheet works fine - on that replacement laptop

Can't try your suggestions at the moment - I'm now at home but will try in the morning when I go to the workshop

  daba 20:07 27 Jun 2006

Have you fully tested the memory in the offending machine ?

Google for memory testers.

Since you state the problem is with Dates, is the Windows Locale set the same as the others, e.g. is the Date format correct, for instance?

Are all the Excel's set-up the same for dates, for example "1904 Date Format" checked or unchecked on all machines - check the Options, Calculation tab.


  Bandy 23:37 28 Jun 2006

Hello daba

Thanks for the thoughts and your interest.
Have just donwloaded and run a quick test on the memory but will do a longer test later. Couldn't do it earlier because broadband in my area has been off all day, and the only memory tester I had was an old one.

Yes the dates are all set the same and the locale is the same in all cases.

I tried the "grumpy" machine with a smaller speadsheet but still the same problem persisted.

I then lost my temper and took out the partition, re-formatted the drive and then re-installed windows and excel again.

Once again the spreadsheet and pivot table are working correctly. Now that broadband is back I'll download the appropriate updates and pray that it stays working for a while .

  Bandy 09:59 04 Jul 2006

Just what I hope is a final comment.

Following the re-install everything appears to be working fine, with all updates etc fully completed.

It has now been working fine for a few days - but I still have no idea why the thing went haywire.

Thanks to all who offered assistance and I'll tick this as resolved

  silverous 10:19 04 Jul 2006

Glad to hear it...although I can't see what's changed from earlier as you'd said you had previously rebuilt it anyway?

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Fujitsu Lifebook P727 laptop review

Best of the Grad Shows 2017: University of the West of England (UWE)

Best value Mac: Which is the best £1249 Mac to buy

Les meilleures GoPro 2017