My problem is this:
I have a large-ish .xls data file (~10k rows, 10 columns) that I download every week and save on my hard drive.
I have a template file that then runs vlookups into this file and summarizes certain information. I would like to be able to dynamically reference this outside data file (be able to change one cell in the template file, and have it update all the file references to a different week's external data file.) All of this while none of the external files are open.
There are about 50 cells that would have to be coded this way.
A couple things I have tried.
-Indirect.ext (through the morefunc addin) cannot do vlookups
-Harlan Grove's Pull Function: Works, but seems prohibitly expensive in terms of calculation time. Each cell is taking more than 5 minutes to calculate, and doing a bunch of cells at once locks up my xls. Does this sounds right for a vlookup onto a range of more than 10k rows or am I doing something dumb?
I wouldn't mind just copying and pasting each file into a tab in the template workbook each week, but the problem is that I have between around 50 of these "template" workbooks that I would like to have updated every time I get a new data file.
Any thoughts? Please let me know if any of the above is unclear, or if you need any more info. Sorry if this has been posted, I did search of google and this forum and didn't spot anything.
Cheers!
I have a large-ish .xls data file (~10k rows, 10 columns) that I download every week and save on my hard drive.
I have a template file that then runs vlookups into this file and summarizes certain information. I would like to be able to dynamically reference this outside data file (be able to change one cell in the template file, and have it update all the file references to a different week's external data file.) All of this while none of the external files are open.
There are about 50 cells that would have to be coded this way.
A couple things I have tried.
-Indirect.ext (through the morefunc addin) cannot do vlookups
-Harlan Grove's Pull Function: Works, but seems prohibitly expensive in terms of calculation time. Each cell is taking more than 5 minutes to calculate, and doing a bunch of cells at once locks up my xls. Does this sounds right for a vlookup onto a range of more than 10k rows or am I doing something dumb?
I wouldn't mind just copying and pasting each file into a tab in the template workbook each week, but the problem is that I have between around 50 of these "template" workbooks that I would like to have updated every time I get a new data file.
Any thoughts? Please let me know if any of the above is unclear, or if you need any more info. Sorry if this has been posted, I did search of google and this forum and didn't spot anything.
Cheers!
