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

0 votes

Formulas should typically be set to automatic as a response to this query. In my situation, this is not effective.

The second typical response is that an Excel parameter is being affected by a macro. That's not how my issue is at all (xlsx file).

My straightforward formula in Office 2010 is not updating.

The formula is:

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

The spreadsheet is updated for everything else. The appropriate value is in the cell in MainCopy. The value shown is the value from before I updated the data on MainCopy. The value on this page remained the same even after I updated MainCopy.

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 corrected. Is there some obscure setting triggered in this file that is creating this issue?

Nov 16 in Others by Kithuzzz
• 20,660 points
28 views

1 answer to this question.

0 votes

This occurred to me today in a worksheet. The affected cells did not update when I pressed F9 or enabled Iterative Calculation, but they did when I double-clicked the cell and pressed Enter. This was located in the help article titled Change formula recalculation, iteration, or precision after conducting a search in the Excel Help system:

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!

answered Nov 17 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

answered Oct 24 in Others by narikkadan
• 37,660 points
62 views
0 votes
0 answers

What is the formula to keep first two words in a cell over excel?

I want to maintain the first two ...READ MORE

Nov 2 in Others by Kithuzzz
• 20,660 points
25 views
0 votes
1 answer
0 votes
0 answers

MS Excel - SumProduct formula with Loop

1 I have 4 arrays of data where ...READ MORE

Feb 18 in Others by Edureka
• 13,640 points
82 views
0 votes
1 answer
0 votes
1 answer
0 votes
0 answers

How to create DropDown which have dynamic Validation List

In my data table, the columns "Category" ...READ MORE

Oct 31 in Others by Kithuzzz
• 20,660 points
51 views
0 votes
1 answer

Is there a way to lock cells after editing an excel sheet that's on sharepoint?

The Excel Web version (which, based on ...READ MORE

answered Oct 3 in Others by narikkadan
• 37,660 points
86 views
0 votes
1 answer

Is there a way to hide excel formula using Apache POI

In Excel, hiding formulae is a feature ...READ MORE

answered Oct 11 in Others by narikkadan
• 37,660 points
81 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP