up vote 0 down vote favorite
share [g+] share [fb]

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.

link|improve this question
89% accept rate
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? – Mike 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? – Mike 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
feedback

3 Answers

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?

link|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. – Mike Jan 18 '10 at 9:02
feedback
up vote 1 down vote

$ is the mark that is should stay fixed...try

=sumproduct((something=A1)*(somethin=B1))
link|improve this answer
1  
No, the poster says he is dragging the formula down. $A$1 would have it stay fixed. $A1 only fixes the column. – mtone Jan 13 '10 at 18:09
@monotone: You're right, I missed that. – Bobby Jan 14 '10 at 8:16
feedback
up vote 0 down vote

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.

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

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