Inconsistent Calculation Results

All,

I have a large workbook (45Mb) containing a tab at the back where a lot of calculations take information captured on other tabs and parses it across several tens of thousands of rows in a pre-formatted template for upload into a database application for use by others. The upload tab is large enough that autocalc was slowing down response time significantly (~10 sec to recalc the workbook). I added a VBA macro to the workbook that allows the user to temporarily disable autocalc on this last tab long enough to enter any required data in the other tabs. The user then re-enables autocalc on the last tab prior to uploading the results to the db system.

After an initial trial deployment that involved loading all the other 70+ tabs with simulated trial run data, I received feedback that the upload sheet was not generating the correct results. Digging into it, I found that some rows were calculating correctly while other rows were not. On any cell that is not calculating correctly, if I highlight the cell, click in the formula bar at the top, and hit Enter, it will then return the expected result.

Any thoughts on what might be causing this behavior?

VBA macro I'm using to disable/reenable calculation on the upload sheet:

Sub DisableACESCalculations()
Me.Worksheets("ACES UPLOAD").EnableCalculation = False
End Sub

Sub EnableACESCalculations()
Me.Worksheets("ACES UPLOAD").EnableCalculation = True
Application.Calculate
End Sub

Thanks,

Tom

Richard, yes, there are several INDEX() functions involved. What should I know about these types of functions and how can I force them to update consistently?

Are there UDF's involved by any chance? If so: http://www.ozgrid.com/forum/showthread.php?t=34438

Had to get to my work PC to grab an example of one of the formulas that are giving me grief:

=IF(AND(INDIRECT("'" & "." & AJ2 & "'!$F$1")=ALLOCATIONS!$B$17,INDIRECT("'" & "." & AJ2 & "'!$F$2")=(X2&Y2)),INDIRECT("'" & "." & AJ2 & "'!" & AN2),(INDEX(ALLOCATIONS!$E$18:$BB$23,MATCH(INDIRECT("'" & "." & AJ2 & "'!$F$1"),ALLOCATIONS!$B$18:$B$23,0),MATCH((X2&Y2),ALLOCATIONS!$E$12:$BB$12,0)))*INDIRECT("'" & "." & AJ2 & "'!" & AN2))

BTW, most of the index and indirect entries were set up to facilitate dragging formulas to copy down several tens of thousands of rows rather than building sets for each of the input tabs. I'd rather someone put a bullet in my brain than consider rebuilding them all again without the lookups. Heh.

Thanks,

Tom

Update. Appears that using various combinations of Ctrl, Shift, Alt, and F9 will, in fact, force the cells to update. Not quite sure yet which combination is doing what but I think I'm onto something, perhaps the workbook doesn't know the upload cells are dirty and need recalculating even with a manual F9 calculate command.