Excel is not updating cells options formula workbook calculation set to automatic

Question

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

User · Accepted Answer

I had this happen in a worksheet today  Neither F9 nor turning on Iterative Calculation made the cells in question update  but double-clicking the cell and pressing Enter did  I searched the Excel Help and found this in the help article titled Change formula recalculation  iteration  or precision         CtrlAltF9    Recalculate all formulas in all open workbooks  regardless of whether they have changed since the last recalculation    CtrlShiftAltF9    Check dependent formulas  and then recalculate all formulas in all open workbooks  regardless of whether they have changed since the last recalculation       So I tried CtrlShiftAltF9  and sure enough  all of my non-recalculating formulas finally recalculated

User · Answer

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

User · Answer

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    create workbook WbDst  copy sheet WsTempl1RefDef into it  references were locally created  copy sheet WsTempl2RefUsr into it   Here as well the Ctrl SHIFT ALTF9 nor Application CalculateFullRebuild worked on WbDst     The following worked    create workbook WbDst  move  not copy  sheet WsTempl1RefDef into WbTempl2    we do not have to save them   copy sheet WsTempl1RefDef into WbDst copy sheet WsTempl2RefUsr into WbDst

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to excel