need an alternative to INDIRECT.EXT function...

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!
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Watch MrExcel Video

Forum statistics

Threads
1,114,061
Messages
5,545,763
Members
410,704
Latest member
Cobber2008
Top