Auto+Hide+Values Not Converting ALL jet-formulas cells into values


I have been building a relatively huge Excel file.  Per each run it grows up to ~45 to 50 MB. In the report, the number of columns goes up to 100 and the number of rows goes up to 14000. And there are some other auxiliary worksheets (with or without Jet formulas) for VLOOKUP() in this big worksheet. For every single sheet in this workbook (while in design mode). I always have "Auto+Hide+Values", correctly spelled and capitalized, in cell A1.

The file has been doing fine by converting cells with NL, NP, NF and GP into values. When I open the file, i will just see the cell in a form of ="XYZ". However, at some point of time, after each run, most of them are converted into values, and some of them remains as its original formula

(e.g. =IF($I11="Purchase",IFNA(VLOOKUP($AD11,ARandomTable,2,0),IFERROR(@IF(MID($AE11,1,2)="FI",NL(,"Purch. Inv. Header","Shipment Method Code","No.",$AE11),NL(,"Purch. Cr. Memo Hdr.","Shipment Method Code","No.",$AE11)),"")),"-")). It used to be converted into something like ="ABC"

Now when I open this file, these NL NP NP formulas forced a recalculation inside the Excel application and sometimes can crash (after a 5~10 minute recalculation). I work around by opening a new workbook and change the calculation options from automatic to manual. Then open the workbook that i have run the jet report in the background. In this way, it wont trigger the recal.

Any thought is welcome. I have tried different things like shortening the table names, destroying pivot cache, duplicating the worksheet and deleting the old one, going back to design mode and rerun, removing the "@" sign during design mode... the last thing i may have to do in the hope of fixing this is probably to rebuild the worksheet / workbook.  




1 comment

Please sign in to leave a comment.