my  block

where uzairs roam

I’ve been working with Excel a lot these past few months. At least half my time these days is spent writing and running spreadsheets and interpreting the results I get from them. In many cases, I run a lengthy batch of calculations overnight and then have Excel produce a pivot table and save the results somewhere. This has forced me to learn some of the bizarre things Excel does (Banker’s rounding, anyone?), the most irritating of which has been this:

(The blacked out bit is just the name of my spreadsheet.)

That one is followed immediately by this:

And what, pray, does that mean? Incomplete calculation? OK…except my workbook contains no calculations at all (I’m saving down only values and my pivot table — no formulas), and I get this error when refreshing my pivot table. Invalid field name? Clearly not — I can see the columns are correctly labelled and everything. Naturally, I ran the job by hand and tried refreshing the pivot table to see if I could reproduce the error. Nope. Google? Nope — nothing interesting there.

It turns out the problem is the fact that underlying a pivot table is a pivot cache. This is an Excel object that stores the actual representation of the table in memory and is saved down with your spreadsheet. My jobs would run the calculations, display the results and the pivot table, and then copy them to a fresh workbook in order to save them down. The problem was that I would then close the workbook where the calculations where actually performed: since the pivot table had been created in it, the pivot cache was stored in there, and since it wasn’t saved after the batch was complete (all the results were saved to a new sheet), the pivot cache was lost. So, what Excel was actually trying to say was that it no longer had an internal representation of the pivot table and therefore could not refresh it.

The solution was simply to change the order in which I did things: I now perform my calculations and display the results, and then copy them to a new workbook before producing the pivot table. In this way, the pivot cache is created in the workbook that is to be saved, and there’s no longer a problem.