0

I have a workbook with over 2,800 sheets (all but 4 hidden). One of the things I've been noticing is that with a workbook this large, Excel seems to stop doing calculations automatically (e.g. when I switch sheets, I have to click the "Calculate Now" button to get the values to display even though the workbook calculation is set to "Automatic").

That hasn't been a problem until now. Two of the visible sheets summarize the data from the sheets that are hidden, and I'm now wanting to compare the data on the two sheets. However, when I reference the other sheet, the value returned is "#REF" because the value is only calculated when I am on that sheet and click the "Calculate Now" button.

As a workaround, I can move the contents of one of the two summary sheets to the first summary sheet so all the data is on a single sheet, but I have to think there should be a simpler way to do it. Here are the things I've tried so far:

  1. Use the INDIRECT() function to try to trick Excel into reevaluating the other sheet.
  2. Use several combinations of Ctrl/Shift/Alt+F9 to force the entire workbook to recalculate (see here: link).

Neither of these options have worked.

Edit:

Answers to questions in the comments:

  • Why am I doing this? I run FEA simulations on water cooled commercial chillers for Carrier Corporation. These systems have hundreds of heat transfer tubes which are held in place at either end of the system by what we call "tubesheets". The holes into which these tubes are inserted are relatively close together and are therefore where the highest stress generally is located. One of the means of characterizing this stress is to find the average maximum principal stress across the surface of the ligament between two tube holes at the thinnest location. I can get the stress from Ansys along a path at that location, but I have to calculate the average in Excel, which I can do by exporting the data from Ansys to a TSV file, which gets imported into Excel. Since the system I'm looking at as 348 ligaments between tube holes, and since I'm comparing 5-8 simulations (the data for which was imported by VBA), the workbook ends up being ~22 MB.
  • Although it takes quite a while to open, Excel doesn't really end up using that much RAM (~480 MB) once it is open. Having said that, resource usage is not really that much of a concern to me. I have both a laptop and a desktop, and because I run my simulations on my desktop, it is a beast (256 GB of RAM, 24 cores, 24 GB graphics card, 2.4 TB hard drive). However, I believe even my laptop would be fine with it since it has 32 GB of RAM. It is somewhat inconvenient because things are generally more sluggish than normal, but aside from the problem described above, I haven't had any issues.
Community
  • 1
tlewis3348
  • 213
  • 1
  • 2
  • 11

1 Answers1

0

Based on the answers here and here it sounds like Excel (not surprisingly) becomes unstable when used with extremely large workbooks. One of the early symptoms of this is that the workbook stops automatically calculating. There are therefore two possible solutions:

  1. Use VBA to open the source data file, run whatever calculations on the file are necessary, and enter the resulting values in the appropriate cells on a summary sheet.
  2. Have a large workbook that contains all the imported data sheets and a single summary sheet. Then copy/paste the values from the summary sheet into a new workbook where the values from the summary sheet are then used in further calculation.

Option 1 is more robust since the risk associated with large files is avoided completely. Option 2 is simpler and may result in fewer issues down the line since changing a calculation on the summary sheet would not require the VBA to open all the TSV files again (which would take several hours). All that to say, Option 2 should be used unless and until the large workbook becomes corrupted to the point where it is unusable.

tlewis3348
  • 213
  • 1
  • 2
  • 11