CHiZZLEDSKi
New Member
- Joined
- Sep 19, 2006
- Messages
- 2
Hi all,
Scenario:
- Column A has numbers in each Row in sequence from 1000 to 2000.
- Column J is supposed to bring back the contents of a specific, constant cell address from a workbook in another folder (which contains workbooks containing the same numbers in the filenames as in Column A)that has the same number in the filename as under Column A in the particular Row.
- For argument's sake, all the workbooks in said folder have filenames starting with "P" followed by the four digit number, for example "P1001.xls"
The following formula is working successfully (entered in cell J5)...
=INDIRECT.EXT("'H:\SALES\[P"&A5&".xls]Sheet1'!$P$9")
I'm able to drag this formula down and it works by changing only the Row in Column A.
Problem:
- It's too slow when working with 1000+ rows. Takes forever to open a the workbook, even if I choose not to update, it still calculates cells forever.
- I am not familiar with Macros, so I'm hoping there is another way to go about this...
Simply need a formula that, when dragged, will take the next numeric part of a filename in sequence, either based solely on increasing sequentially the numbers in the filenames themselves, or utilizing the data in another cell on the same row such as in the above INDIRECT.EXT formula example.
Cheers!
Scenario:
- Column A has numbers in each Row in sequence from 1000 to 2000.
- Column J is supposed to bring back the contents of a specific, constant cell address from a workbook in another folder (which contains workbooks containing the same numbers in the filenames as in Column A)that has the same number in the filename as under Column A in the particular Row.
- For argument's sake, all the workbooks in said folder have filenames starting with "P" followed by the four digit number, for example "P1001.xls"
The following formula is working successfully (entered in cell J5)...
=INDIRECT.EXT("'H:\SALES\[P"&A5&".xls]Sheet1'!$P$9")
I'm able to drag this formula down and it works by changing only the Row in Column A.
Problem:
- It's too slow when working with 1000+ rows. Takes forever to open a the workbook, even if I choose not to update, it still calculates cells forever.
- I am not familiar with Macros, so I'm hoping there is another way to go about this...
Simply need a formula that, when dragged, will take the next numeric part of a filename in sequence, either based solely on increasing sequentially the numbers in the filenames themselves, or utilizing the data in another cell on the same row such as in the above INDIRECT.EXT formula example.
Cheers!
