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
  • Just out of morbid curiosity... _why_?? – Alex M Apr 29 '20 at 21:56
  • Have you had a look at the possibilities listed here: https://superuser.com/questions/836324/cells-not-updating-automatically – Justin Doward Apr 30 '20 at 01:16
  • Honestly I'm shocked that you are able to work around 2800 sheet,,, although limit is 255 sheets, but Excel doesn't restrict number of sheets one could add. The big factor is ultimately limits the number of worksheets your workbook can hold,, is your computer's memory. Could you please write about Hardware configuration !! – Rajesh Sinha Apr 30 '20 at 09:16
  • @AlexM See the edit to my post. – tlewis3348 Apr 30 '20 at 21:07
  • @RajeshS See the edit to my post. – tlewis3348 Apr 30 '20 at 21:07
  • @JustinDoward I had not looked in detail at all the answers. The closest options as answers were either here (https://superuser.com/a/855644/297949) or here (https://superuser.com/a/878510/297949). Both involve basically deleting and recreating the spreadsheet. If that's the solution, then so be it, but I don't think doing that is workable for me since it takes several hours to import all the data. – tlewis3348 Apr 30 '20 at 21:19
  • Are there any macros in the workbook that may be causing an issue? Most likely it is just the sheer number of sheets resulting in a corrupted wirkbook, I could try a macro to copy the sheets to new workbooks limited to 255 sheets per book then some form of indexing. Is it really necessary to have all the data spread across so many sheets? Normally I would suggest merging the data somehow into a single sheet or a few sheets and then extracting the data into a viewing sheet. Are the sheets all the same or similar format and could they be merged? – Justin Doward Apr 30 '20 at 22:28
  • I just saw your updated post, can I clarify that each ansys import is creating a 2800 page "report" which you then access to average the results? – Justin Doward Apr 30 '20 at 22:48
  • Hi @tlewis3348,, if the edited part is the answer, then please [edit] your post & delete the edited part, and post it as an answer,,[for better understanding, check this](https://superuser.com/help/self-answer). – Rajesh Sinha May 01 '20 at 08:19
  • @JustinDoward There are macros in the workbook to import data. I suppose it's not absolutely necessary to have the data spread across multiple sheets, but it does make both the import and the use of the data much simpler. With the import, I don't have to figure out where the data I'm importing needs to end up on the sheet. With the use, I can build the sheet reference based on the naming convention I've used for the names of the sheets. If everything were on a single sheet, I'd have to find a means of finding the data in the sheet. These things aren't impossible, but are inconvenient. – tlewis3348 May 01 '20 at 12:25
  • @JustinDoward Each import of data from Ansys is a 7KB or 8KB TSV file with 12 columns and 64 rows of data (some rows are not completely full). There are ~2800 of these that have been imported into a single spreadsheet. Note: I did try referencing the data I need from the file directly from the formula, but that only seems to work when the file is open, which defeats the whole purpose of using that approach to start with. I suppose the better approach for the future may be to use VBA to calculate the average I need and enter that number into the appropriate cell on the summary sheet. – tlewis3348 May 01 '20 at 12:35
  • @RajeshS Sorry. That was poorly worded. The answers are to questions asked in the comments here. I have fixed the wording to more accurately reflect that. – tlewis3348 May 01 '20 at 12:36
  • I would recommend running a macro to combine the 2800 worksheets into a single sheet, 2800 x 64 rows is almost irrelevant to excel. I can have a look at this tomorrow. I assume that the formats are the same (blank rows are fine so long as the overall format is the same). If you could post an example of the format of each sheet that would help a great deal. – Justin Doward May 01 '20 at 13:00
  • @JustinDoward Yes. And writing the code to add the new data to the bottom of the sheet is certainly doable, but then you have to find a means of getting the data from a specific location on that sheet for use in the summary sheet. This is certainly doable (assuming that you've entered the source file name in an additional column), but it would certainly make the formula to get the data more complicated than it already is. – tlewis3348 May 01 '20 at 16:54
  • I suggest you move to a single sheet rather than working from 2800 sheets, it will make your life easier in the long run, simply add each import to the new sheet I think formula to extract the data will be easy enough. If you want some assistance adapting the existing workbook let me know. – Justin Doward May 01 '20 at 17:58

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