Copy function to new cell gives incorrect result

[tlp]

I'm having a problem. In B1 I have ABC, in B2 I have DEF. In A1 I type =lower(B1) and it gives me abc. I copy A1 to A2, A2 now has lower(B2),and it gives me abc, not def. If I type =lower(B2) into A1 it correctly gives me def. The same behavior happens for every function I copy form one cell to another. I'm running excel 2007. I've run a repair and reinstall, no change. What's wrong?

 

[rallcorn]

When you copy formula down & get wrong results, if you press F9 does it correct itself? If so, you have manual calculation set instead of automatic

 

[tlp]

Yes, that fixes it. What do I need to change so I don't have to hit F9?

 

[rallcorn]

Go to Office Button (extreme top left)
Excel Options
Formulas
Workbook Calculation - click Automatic