Some Excel formulas not calulating

I have a rather infuriating issue with a specific Excel worksheet - some of the formulas do not calculation at all unless you press F2 (to edit the cell) and press Return.

This forms part of an advanced VBA system and as such I don't want any user interaction.

It is only a specific block of formulas that are refusing to calculate. These all have formulas such as below:
=INDEX(RowSource,$A20,C$7)

Open in new window


(Where 'RowSource' is a workbook-level named range on another sheet consisting of 4 columns and about 200 rows, 'C$7' is an integer that represents the correct column and '$A20' changes each row to indicate which row in 'RowSource' to lookup)

I have checked all the obvious causes:
Sheet EnableCalculation is set to TRUE (besides other sheet formulas calculate)
The formulas are formatted as 'General'
The formulas are showing values - not the formula text
The calculation mode is set to 'Manual' (an essential part of my system)
There is no cell prefix character

The only way I have been able to get the formula to fire / value to update is using the F2+Enter method or a Shift+Ctrl+Alt+F9 to rebuild all (but I don't want to do that when live since I only want that single worksheet calculated).

My design has the worksheet containing the 'RowSource' cells hidden and I have even tried making that sheet visible before i try to calculate - no dice.

Any suggestions would be most appreciated
LVL 3
AL_XResearchAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
If Calculation Mode is set to Manual as you have stated, nothing other than the active cell will update until you force recalculation. Pressing F9 does the workbook, Shift + F9 just does current sheet.

Thanks
Rob H
0
ProfessorJimJamCommented:
change the calculate method to manual then select the whole range and then press F2 and then press Control + Enter
0
AL_XResearchAuthor Commented:
Rob: Sorry it appears that in my speed I was less than specific.

I am debugging this part of my system (interrupting my code before it tries to recalculate and displaying the workbook and relevant sheet) and manually forcing calculation by  pressing Shift+F9.

The majority of formulas on the sheet update as I would expect but only the 'Index' block of cells does not.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Rob HensonFinance AnalystCommented:
If the RowSource is on a hidden sheet and you are pressing Shift+F9 on a different sheet, that key combo only does current sheet; pressing F9 does whole workbook.
0
AL_XResearchAuthor Commented:
ProfessorJimJam: Yes that certainly works maually but as I said I don't want any manual interaction.

The VBA equivalent would be setting the 'FormulaR1C1' property of the range to a fixed value but
a) I shouldn't have to do this
b) it suggests that something is wrong with the sheet / formulas (although I can't see anything wrong with such a basic formula)
c) the fact that it happens in only that sheet of every file I generate using that code seems to suggest the formulas are at issue and yet I have used just the same formulas on many other sheets in that and other workbooks and never had an issue.

I am afraid I can't post the workbook or code due to confidentiality and the complexity of the code that generates the workbook (several large functions).
0
AL_XResearchAuthor Commented:
Rob: The 'RowSource' range (and it's parent worksheet) do not have to be calculated - they are all constant values (i.e. no formulas).
0
Rob HensonFinance AnalystCommented:
Is "RowSource" a Static or Dynamic Named Range?

If Dynamic it will have to recalculate the Range even if the values in it are constant.
0
AL_XResearchAuthor Commented:
It is a static fixed sized range that is created long before the sheet is built
0
Rob HensonFinance AnalystCommented:
Not a fan of Sendkeys (a sentiment held by a number of people on here) but:

Range("A1:F6").Select
SendKeys ("{F2}^{Enter}")
Range("A1").Select

This will do the same as ProfJJ has suggested but VBA rather than manual. Change the Range accordingly.
0
AL_XResearchAuthor Commented:
Thanks rob. I agree 100% with the SendKeys sentiment - I have always found that way of doing things to be wrong. If only way you can do it is with SendKeys then it is probable that either you sought be doing it or your need to do a bit more research & rethink the design.

In fact since the block has a consistent set of formulas you could simply set the 'formula1C1' properly - which is the same add pressing F2+Enter

I however don't want to add an extra step and potentially an extra delay (every second counts).

Does anyone have an idea why this is happening ? Past experience with this sort of issue ?
0
Rob HensonFinance AnalystCommented:
The only other things that I can think of are whether you have either/both of these statements earlier in the routine

Application.ScreenUpdating =False
DisplayEvents = False

If you have these at the beginning and are stepping through manually but then stopping before the end they may not reset correctly.

ScreenUpdating will reset automatically without an =TRUE statement on completion of the script but not if you break out early
DisplayEvents needs the =TRUE statement to reset.

Thanks
Rob H
0
Saqib Husain, SyedEngineerCommented:
Have you tried the Range.calculate method?
0
AL_XResearchAuthor Commented:
Rob: I amended my code so that the last 2 lines print out the values of 'Application.screenupdating' and 'Application.EnableEvents' and below are the results:

application.ScreenUpdating = True
application.EnableEvents = False

The line after that is 'Exit sub' and when I test the same settings I get:

application.ScreenUpdating = True
application.EnableEvents = True

(Somehow Excel has turned the 'EnableEvents' propert).

All that is of no matter because we are still left with 80% of the formulas updating whereas 20% refuse.

Saqib: If you look back at the previous posts you will see that I have tried nearly every combination of recalc that there is:

If I tried 'activesheet.calcaulate' then nothing happens
If I selected all the problem cells and tried 'selection.calculate' (the same as 'Range.calculate' then nothing happens.

Interestingly the issue cells are in the same columns that are covered by another named range called 'RowHeaderCols' and if I try the following the cells recalculate !!

[RowHeaderCols].EntireColumn.calculate

Open in new window


That is very strange because the Eval function is returning a range object which means this command is the same as the one that previously did not work !
0
AL_XResearchAuthor Commented:
I should explain that this workbook (upon which are the problem formulas) forms a 'data-module' that is opened by VBA and it's data loaded into a database via some third-party formulas. Having the correct values in these 'row header' cells is therefore vital to load the data into the correct locations in the database.

I can re-run the load (as I have modified the code to stop before the problem) as many times as I like and each time it will 'reset' back to the same state.

I have just re-loaded the 'module' several times and can confirm that:

Shift+F9 never recalculates the issue formulas
F9 never recalculates the issue formulas
'activesheet.calculate' never recalculates the issue formulas
'selection.calculate' now does calculate (didn't previously) the issue columns - no matter what the selection is (i.e. all sheet cells, the issue columns or the issue cells)
The code '[RowHeaderCols].entirecolumn.calculate' will recalculate the cells
Ctrl+Alt+F9 does recalc the issue formulas
Ctrl+Alt+Shift+F9 does recalc the issue formulas


I am baffled here. I have never come across a situation were a specific range can be calculated but calculating the workbook or worksheet that includes those cells does not work
0
AL_XResearchAuthor Commented:
Note: I am using Excel 2010

Selecting the 'Calculate Now' (equivalent of F9) and 'Calculate sheet' (equivalent of Shift+F9) buttons from the Ribbon' s 'Formula' tab both do not recalc the problem formulas
0
AL_XResearchAuthor Commented:
Interestingly if I use the 'Evaluate Formula' function (from the Ribbon), on an example issue cell, whilst each step appears correct the resulting value is the same incorrect static value I keep getting.

The cell when properly evaluated returns a different value (the one I would expect)
0
AL_XResearchAuthor Commented:
I think I have it !!

Corrupt sheet

I copied all cells of the problem sheet in the template file to a new worksheet (via copy and paste rather than the sheet copy/move function), created some VBA to transfer all named ranged from the old to  the new sheet and as soon as I loaded the book all the cells were calculated correctly as this should be !
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AL_XResearchAuthor Commented:
I was unable to find out why those specific formulas were not calculating (i.e. why there was corruption there and how it occured / to prevent it in the future) but this was definitely the solution
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.