Simple formulas not working when drag to new cells

[John_Whin]

I've got problems on several sheets in the same workbook this morning. On existing sheets even simple functions are not working. I've added a column for example.

in M13 I've got =K13. so I drag that simple formula up and down. (When I manually enter the =cellreference) it works fine. When I add a sheet and do same (even drag formula) it works OK.

But on existing sheets it is malfunctioning.

M11 contains =K11 but returns K13
M12 contains =K12 but returns K13
M13 contains =K13 and returns K13
M14 contains =K14 but returns K13
M15 contains =K15 but returns K13

First I did reopen the spreadsheet, did restart computer and did shut down and then start computer fresh. Also saved as the original file but problem persists.

 

[Joe4]

Sounds like maybe your calculation mode is set to "Manual" instead of "Automatic".
If you press F9 (which is manual calculation), does it then show the right numbers?
If so, that is the problem. Change the Calculation to "Automatic" and it will work the way you expect.

 

[John_Whin]

YES,YES, YES. I had this problem on several sheets, looks like I have to hit F9 on all the sheets but it is back to normal.

Thank you very much. I have no idea when I hit F9 in the first place, maybe when the keyboard legs slipped and I must have accidentally bumped that key. Is there a way to apply the Automatic to all the sheets with one action?

You folks and this site are fantastic.

 

[John_Whin]

Oh and here is a different quote for you

"Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"


"Give a man a fish, feed him for a day. Teach a man to fish, and he'll be drunk in the boat for life"

 

[Joe4]

Hitting F9 doesn't toggle it between Automatic and Manual. F9 simply forces a recalc.

There is a setting that you need to change. See here: https://support.office.com/en-us/ar...93-0367-41ce-b892-dfe54946bd76#__toc305944076

 

[John_Whin]

Yep, for some reason that was reset from Automatic to Manual in that workbook, looked in several other ones and they were all still on Automatic. I have no idea how it was changed in the first place. I'm the only one who uses the computer and has access to the workbook.

I'm good now, thank you for the help.