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!
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Forum statistics

Threads
1,141,924
Messages
5,709,372
Members
421,633
Latest member
Ubergribbler

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top