Linking to external workbook
Sorry if you've read this. However, I didn't see the question appear in my
reader. I have a project that includes parsing a .txt file into Excel. Included in the fields imported is an EmplID. I need to retrieve data from separate workbooks based on this. My experiment with Index and Match produced slow results when linked to one other workbook (about a minute for 4000 records). I am currently looping using i to increment the row number in the lookup formula. Is there a faster method (either not using Index/Match or not looping)? Steve Steve |
Linking to external workbook
If you open the other workbook, things will probably go a lot faster.
-- Regards, Tom Ogilvy "Steve" <No Spam wrote in message ... Sorry if you've read this. However, I didn't see the question appear in my reader. I have a project that includes parsing a .txt file into Excel. Included in the fields imported is an EmplID. I need to retrieve data from separate workbooks based on this. My experiment with Index and Match produced slow results when linked to one other workbook (about a minute for 4000 records). I am currently looping using i to increment the row number in the lookup formula. Is there a faster method (either not using Index/Match or not looping)? Steve Steve |
Linking to external workbook
Also, you can enter a row oriented formula template in one command
Range("B2:B100").formula = "=Index([Book1.xls]Sheet1!$A$1:$A$1000,Match(A2,[Book1.xls]Sheet1!$F$1:$F$10 0,0),1)" as an example -- Regards, Tom Ogilvy "Steve" <No Spam wrote in message ... Sorry if you've read this. However, I didn't see the question appear in my reader. I have a project that includes parsing a .txt file into Excel. Included in the fields imported is an EmplID. I need to retrieve data from separate workbooks based on this. My experiment with Index and Match produced slow results when linked to one other workbook (about a minute for 4000 records). I am currently looping using i to increment the row number in the lookup formula. Is there a faster method (either not using Index/Match or not looping)? Steve Steve |
Linking to external workbook
Tom
Thank you ... for this and the tip to open the other workbook. When testing, I noted that writing a loop within a workbook was far quicker than the code I have looping to an external workbook. Would I be correct in thinking each time the formula is written during a loop, Excel look for the external connection, despite calculations and screen updating being off? Steve "Tom Ogilvy" wrote in message ... Also, you can enter a row oriented formula template in one command Range("B2:B100").formula = "=Index([Book1.xls]Sheet1!$A$1:$A$1000,Match(A2,[Book1.xls]Sheet1!$F$1:$F$10 0,0),1)" as an example -- Regards, Tom Ogilvy "Steve" <No Spam wrote in message ... Sorry if you've read this. However, I didn't see the question appear in my reader. I have a project that includes parsing a .txt file into Excel. Included in the fields imported is an EmplID. I need to retrieve data from separate workbooks based on this. My experiment with Index and Match produced slow results when linked to one other workbook (about a minute for 4000 records). I am currently looping using i to increment the row number in the lookup formula. Is there a faster method (either not using Index/Match or not looping)? Steve Steve |
All times are GMT +1. The time now is 11:23 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com