I have to extract cell values into a summary spreadsheet from multiple closed workbooks saved in Google Drive.

I'm familiar with Laurent Longre's INDIRECT.EXT, but run into a problem while trying to use INDIRECT.EXT inside an INDIRECT expression that combines various worksheet paths and names from the their lists in an open summary worksheet. I suspect the problem may be in the 'quote the quotes' mess in the concatenated expression, but can't find anything wrong in the formula.

My question: is the combination of these two functions, i.e., INDIRECT.EXT inside INDIRECT, allowed? I do know cell ranges and defined names cannot be used inside INDIRECT.EXT.

On Monday, November 11, 2013 12:30:27 PM UTC-5, wrote:
I have to extract cell values into a summary spreadsheet from multiple closed workbooks saved in Google Drive.


For the benefit of others interested in this very useful functionality, an example below should provide a quick start:

=INDIRECT.EXT("'"&A1&"["&A2&"]"&A3&"'!B$1")

where, for example,

A1 contains the directory/folder path, such as C:\User\My Documents\Me\Dropbox\ (keep the final backslash!)
A2 contains the workbook name, such as Summary.xls
A3 contains the worksheet name, such as Data, and
B$1 is the cell address with the data of interest.

Pay attention to the order of single and double quotes!