Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

If you write a formula like:

=sumproduct((something=$A1)*(somethin=$B1)) 

Giving a result of 8. Usually the references and results change as you drag it down into other cells. Well,the references do, but the result doesn't!?

It used to work, but just stopped doing it a few minutes ago (after it crashed actually).

I know it will be something silly, but what just happened? How do I make it work as it should. What am I doing wrong...why me!

Help is gratefully appreciated.

Thanks Mike.

share|improve this question
    
And to cap it all off - when I try to use the Format as Table option ALL the tables are 'blued out'. What have I done. And what can I do? –  RocketGoal Jan 13 '10 at 14:32
    
It seems that if I double click into each cell then tab out the formula's refreshes itself and gives me the correct result in the cell...any ideas what's gone belly up? –  RocketGoal Jan 13 '10 at 14:57
    
Is the function syntax still correct after the program crashed? –  Mark Jan 13 '10 at 15:50
    
Can you post a picture of what your excel spreadsheet looks like? –  Mark Jan 13 '10 at 16:00

3 Answers 3

up vote 1 down vote accepted

It seems your formulas are not automatically updating. Pressing F9 will force a manual recalculation and should recalculate.

To fix it, click the big Ribbon Button, go in Excel Options. Choose the Formulas panel, and choose Automatic Workbook Calculations.

Now the question is: was something else broken too?

share|improve this answer
    
it does sound like calculation is set to manual and this is the likely solution –  datatoo Jan 13 '10 at 18:37
    
I've opened Excel this morning and it's back to normal - pheew! I followed your instructions anyway just to see if the correct buttons were ticked, which they were. I'm sure that the crash must have defaulted automatic workbook calculations to manual. The system must have been reset over the weekend, but if it happens again I'll know next time. Many, many thanks. –  RocketGoal Jan 18 '10 at 9:02

Go to Options >> Advanced and select the Enable fill handle and cell drag-and-drop check box. This should fix this issue.

share|improve this answer

Whenever something stops working after a crash, there is the possibility that something got corrupted. I would start with repairing your office installation. That should replace all of your Office related program files while leaving your settings and data untouched. If it's still busted, then you should try a re-install.

Sadly, that solves more Microsoft problems then you would expect.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.