Using INDEX() function and named range to get data from the source file

Hello,

Not sure my approach of fetching data from an external file is an efficient way. In the source file, I defined the named ranges. Then in the workbook, I used the INDEX() function and those named ranges to get data from the external source file. For example, =INDEX(SourceFile!Range1, 1) is to get the first cell in the named range "Range1".

Due to the size of the source file and workbook (both have 5000 rows and 100 columns, over 50MB), I found this approach may have efficiency problem, as I had to wait over 15 minutes to get the file updated and crashed in a few occasions. Is this because the file size is too large? Or the INDEX() function is not efficient?

I appreciate your great help for this problem.

Hi,

I assume first that you have both files open when you are attempting to refresh the data. Updating external links with a closed file is incredibly slow. Also, the use of the INDEX function may also be slowing you down, but it really depends on how your data is presented and how you expect to pull it through. You are trying to link to 5 million pieces of data here, so unfortunately it is going to take Excel a long time to deal with this, regardless of the approach that you take. Could you not explore using an Access data base to store the data, and then pull the required information into the spreadsheet using either a Pivot-table or a data-table for example? This would generally be a better approach.

I would also question why you need to link to 5 million pieces of data in the first place? Could you not perform summary activities within the source workbook, and then link to the summaries instead? This would certainly help.

I appreciate that this doesn't really answer your query, but I do think a fundamental redesign is probably in order for this to work efficiently.

Kind regards,
Simon.

Is there more idea?