Hi,
I have a process that was big and cumbersome, so I split it into two workbooks. The first workbook links to an external data source (this was the part which slowed the process down before I split it into two), the second workbook pulls in the data from the first one, and does a little processing, but mainly just summarizes the data. Each cell in the second workbook that pulls data from the first is just a simple direct link - so cell c10 in the relevant tab of the second workbook has the simplest formula:
=[first workbook]Tab!c10
It all works fine. But...
When I try to save the second workbook, it always crashes Excel. The only time it doesn't crash Excel is if the first workbook (that it links to) is open. So my assumption is that it must be something to do with the links to the first workbook. It's a big set of data - there are over 60,000 links to the other workbook. That sounds a lot, but the files are still relatively small (3.5MB for the first and 1.1MB for the second workbook), and once the data has been populated in the first one, they are very quick to recalculate and for the links to refresh.
I have a theory that when the first workbook is open and the formulae in the second workbook are simple (as above) Excel can handle it. But if the first workbook is closed, then actually the formulae in the second look like this:
='FILEPATH\[first workbook]Tab'!c10
Maybe the longer formulae, including the file path, repeated 60,000 times, is too much for Excel.
Ordinarily, keeping the first workbook open while working on the second wouldn't be a problem. It's mainly tedious because, despite being relatively small, for some reason the first workbook takes 4-5 minutes to open. And I almost never have to work on that one - once a month I update it with refreshed data and then I don't have to do anything else for a month. But I do have to regularly update the second one, so it's painful to have to keep opening the first, and very painful if I forget and then lose my work when Excel inevitably crashes.
Does anyone know of a way to prevent the workbook from causing Excel to crash when I save it?
Thanks
I have a process that was big and cumbersome, so I split it into two workbooks. The first workbook links to an external data source (this was the part which slowed the process down before I split it into two), the second workbook pulls in the data from the first one, and does a little processing, but mainly just summarizes the data. Each cell in the second workbook that pulls data from the first is just a simple direct link - so cell c10 in the relevant tab of the second workbook has the simplest formula:
=[first workbook]Tab!c10
It all works fine. But...
When I try to save the second workbook, it always crashes Excel. The only time it doesn't crash Excel is if the first workbook (that it links to) is open. So my assumption is that it must be something to do with the links to the first workbook. It's a big set of data - there are over 60,000 links to the other workbook. That sounds a lot, but the files are still relatively small (3.5MB for the first and 1.1MB for the second workbook), and once the data has been populated in the first one, they are very quick to recalculate and for the links to refresh.
I have a theory that when the first workbook is open and the formulae in the second workbook are simple (as above) Excel can handle it. But if the first workbook is closed, then actually the formulae in the second look like this:
='FILEPATH\[first workbook]Tab'!c10
Maybe the longer formulae, including the file path, repeated 60,000 times, is too much for Excel.
Ordinarily, keeping the first workbook open while working on the second wouldn't be a problem. It's mainly tedious because, despite being relatively small, for some reason the first workbook takes 4-5 minutes to open. And I almost never have to work on that one - once a month I update it with refreshed data and then I don't have to do anything else for a month. But I do have to regularly update the second one, so it's painful to have to keep opening the first, and very painful if I forget and then lose my work when Excel inevitably crashes.
Does anyone know of a way to prevent the workbook from causing Excel to crash when I save it?
Thanks
