I have an Excel spreadsheet with 5000 rows and columns till AY (size 12MB). Except for the first six columns, the rest contain either vlookups
or other formulae. All the vlookups are in a separate Excel worksheet. I have changed the Excel setting to manually update the links and calculate formulae. Now every time I try to update the links, either Excel hangs or it takes something like 15 minutes.
Right now I am working on splitting the worksheet into four instances; where I will update four different Excel sheets in sequence. But it requires manually copying index columns to next stage. It takes 3-4 hours to update the links. Yesterday I started getting error messages that:
Excel does not enough resources to complete the operation.
I was left with no choice, but to split the entire operation into four small steps requiring manual intervention.
I looked at INDEX. Not really applicable on my case. Any ideas on how I can get it done quickly?