As Stephen I also like to thank Dick for opening his blog to others. So I’ll take the chance to cover some medium to advanced formula issues in my postings.
Excel provides a very powerful function – INDIRECT. It just lacks the functionality to access closed workbooks. The following is a compilation of common alternative solutions presented in the Excel newsgroups:
- Laurent Longre has developed the free add-in MOREFUNC.XLL which includes the function INDIRECT.EXT
- You can use it in the same way as INDIRECT. e.g.:
<span class="text">=INDIRECT.EXT("'C: emp[book1.xls]sheet!'A1")</span>
- Note that in this formula you also specify the path/directory information
- If you use INDIRECT.EXT with an open workbook it behaves the same way as INDIRECT does
There’re some limitations to this function:
- It does not work reliable on all computers. So you have to try it in your individual environment.
- INDIRECT.EXT can only return a single cell reference from a closed workbook. So you can’t use it for example as second parameter in a VLOOKUP function:
<span class="text">=VLOOKUP("search_text",INDIRECT.EXT("'C: emp[book1.xls]sheet!'A1:B20"),2,0)</span>
- INDIRECT.EXT does not work with defined names within closed workbooks.
- If you have to access several closed workbooks your spreadsheet can become slow while re-calculating.
- The usage is described here.
- This function is relatively slow and the data has to be organized in a database like structure (that is a single area with field names as top row).
- Note: Microsoft does not support this addin anymore.
- Code can be found here.
- The function creates separate Excel application instances to “pull” data from closed workbooks.
- This function is more robust, can deal with non-database like layouts and can also deal with cell ranges.
- Example usage:
<span class="text">=VLOOKUP("search_text",PULL("'C: emp[book1.xls]sheet!'A1:B20"),2,0)</span>
All these solutions have one common drawback: They’re quite slow. So use them carefully and don’t use them with large cell ranges.