[excel] Excel is not updating cells, options > formula > workbook calculation set to automatic

The usual answer to this question is to turn formulas to automatic. This, in my case, is not working.

The second usual answer is that there is some macro that is affecting the Excel settings. This is not the case with my problem (xlsx file).

Office 2010, my simple formula is not updating.

The formula is:

=IFERROR(TRIM(OFFSET(MainCopy!AG$3,$A77,0)),"")

Everything else is updating in the spreadsheet. The cell in MainCopy has the correct value. The value displayed is the value before I updated the information on MainCopy. When I put new information in MainCopy, the value on this page stayed the same.

I have:

• Researched this issue on the internet - all solutions easy to find do not address this problem
• Pressed F9 (did nothing)
• Pressed Shift-F9 (did nothing)
• Turned calculation to manual and hit all combinations of F9 (did nothing)
• Turned calculation back to automatic (did nothing)
• Saved with a new name (did nothing)
• Re-updated data on MainCopy (did nothing)
• Selected the cell and hit F9 (did nothing)
• Put my cursor in the box and hit enter (UPDATED VALUE CORRECTLY)

I cannot put my cursor in each box and hit enter, there are too many boxes. In addition, this is creating a major error trap in a spreadsheet that needs to be correct. Is there some obscure setting that has been triggered in this file that is creating this issue?

I had a similar issue with a VLOOKUP. The field I was using to VLOOKUP was formatted as a custom field. Excel was saying it was a number stored as text. Clearing this error (selecting all fields with the error, beginning with the first one with the error and clicking change to Number even though I didn't really want it to be!) fixed it.

On Excel 2016, using Alt+Ctrl+F9 work well.

This combination call Application.CalculateFull() VBA Excel function.

This can be time consuming if other Excel files are loaded because all Excel sheets of all opened workbooks will be calculated again!

I have searched a function to calculate a specific sheet but I don't have found something!

Add this to your macro and it will recalculate all the cells and formulae.

Call Application.CalculateFullRebuild

Hope it has been already fixed.

PS The above code is for the people looking for a macro to solve the issue.

Reference: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-calculatefullrebuild-method-excel/

My problem was that excel column was showing me "=concatenate(A1,B1)" instead of it 's value after concatenation.

I added a space after "," like this =concatenate(A1, B1) and it worked.

This is just an example that solved my problem.

Try and let me know if it works for you as well.

Sometimes it works without space as well, but at other times it doesn't.

In short

creating or moving some/all reference containing worksheets (out and) into your workbook may solve it.

More details

I had this issue after copying some sheets from "template" sheets/workbooks to some new "destination" workbook (the templates were provided by other users!):

I got:

• workbook WbTempl1
  • with sheet WsTempl1RefDef (defining the references used e.g. in WsTempl2RefUsr below, e.g. project on A1)
• workbook WbTempl2 (above references do not exist, because WsTempl1RefDef is not contained nor externally referenced, e.g. like WbTempl2.Names("project").refersTo="C:\WbTempl1.xls]'WsTempl1RefDef!A1")
  • contains sheet WsTempl2RefUsr (uses inexisting global references, e.g. =project)

and wanted to create a WbDst to copy WsTempl1RefDef and WsTempl2RefUsr into it.

The following did not work:

1. create workbook WbDst
2. copy sheet WsTempl1RefDef into it (references were locally created)
3. copy sheet WsTempl2RefUsr into it

Here as well the Ctrl(SHIFT)ALTF9 nor Application.CalculateFullRebuild worked on WbDst.

The following worked:

1. create workbook WbDst
2. move (not copy) sheet WsTempl1RefDef into WbTempl2
   • (we do not have to save them)
3. copy sheet WsTempl1RefDef into WbDst
4. copy sheet WsTempl2RefUsr into WbDst

Go to Files->Options->Formulas-> Calculation Options / Set Workbook calculation to Automatic

the ctrl alt f9 , is the temporary solution , going to options-formula-auto calculate is the right way, that option turned manual, because some shortcut key on being pressed by mistake turns automatic to manual

The field is formatted as 'Text', which means that formulas aren't evaluated. Change the formatting to something else, press F2 on the cell and Enter.