Importing a range of data from a closed workbook

**sigfreid** · 09-09-2009, 10:32 AM

Got them to upload!

**NBVC** · 09-09-2009, 11:19 AM

I got it to work, obviously having to change path to my directories...

Try:

=INDIRECT.EXT("'C:\Documents and Settings\hughel13\My Documents\["&Reports!$C$11&".xls]Sheet1'!$R"&ROW($A2))

Copied Down

**sigfreid** · 09-09-2009, 11:47 AM

Brilliant, that's exactly it! Is there anyway to speed it up do you know? I'm using it for 3 columns each 750 rows down and it takes a good 30+ seconds to calculate it, whereas if the target workbook is open it's pretty much instant, it's also instant if I set an absolute file name rather than a concatenated one, which seems odd because the other extra work it has to do as far as I can tell is putting the text together. Or is this an unavoidable limtiation of working with data from a closed workbook?

**NBVC** · 09-09-2009, 11:51 AM

You can add the FALSE parameter allowed for making the function non-Volatile:

=INDIRECT.EXT("'C:\Documents and Settings\hughel13\My Documents\["&Reports!$C$11&".xls]Sheet1'!$R"&ROW($A2),FALSE)

per Indirect.Ext help: hit Ctrl-Alt-F9 to update the results