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:
(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