CONCATENATE within INDEX function

[dagu85]

Hi, I've been scouring the internet to find a solution to this problem with no avail. I'm trying to use the INDEX function to return a value from a second workbook using a path that's put together using values in the first workbook. Both workbooks are in the working directory.

I have a cell in workbook 1, B12, which takes a user input and corresponds to name of the worksheet within workbook 2 that i want to look in.

For example:
The name of workbook 2 is: "Tubing Spreadsheet.xls"
The value of cell B12 in workbook 1 is: 7

I've been trying to use INDEX as follows:

=INDEX('CONCATENATE("[Tubing Spreadsheet.xls]",B12)'!$A$1:$G$100,...)

but this doesn't work. I've also tried creating the concatenated string in its own cell and then calling on that cell within INDEX but I can't get that to work either. Is what I'm trying to do possible?

I'm trying to do this without using a VBA and I'll also need to do this within the MATCH function later on. Thanks in advance.

 

[Asala42]

You need to use the INDIRECT function for this kind of thing to work - however this requires the file be actually open. If this is acceptable the structure would look something like this:

=INDEX(INDIRECT("'[Tubing Spreadsheet.xls]"&B12&"'!$A$1:$G$100",...)

The MOREFUNC add in has a similar function (I believe it's called INDIRECT.EXT) that will work on a closed workbook.

 

[dagu85]

Thanks Asala, this works great. I would prefer to not require the other workbook to be open but that's not too big of a deal.

My experience with add-in's is nonexistent. If I download the add-in and then send the file to someone else will it work as normal for them?