Help Using Concatenate with Index Match

Hello, I am trying to combine the Index/Match functions with the Concatenate Function.

I have two sheets (Sheet 1, Sheet 2). I want cell A1 in Sheet 1 to index and match a cell in Sheet 2. Sheet 2's name will change several times throughout the year, so I just want to be able to type the name of Sheet 2 into a cell B1 in Sheet 1 and have A1 reference that file name from the name in B1.


The Index/Match formula is simple (and works) if the file name for Sheet 2 stayed static. In this example, A1 is a color. It will reference the cell in Sheet 2 that matches "Fruits" and return to me the item in the Items Column.
INDEX('C:\Files\Spreadsheets\Sheet2.xls'!Items,MATCH($A$1,'C:\Files\Spreadsheets\Sheet2.xls'!Fruits,0))


Since the name is not static, I need to use the concatenate function to type in the new year and month (displayed as 2013.10 for October 2013) that each file is saved by. The file name would be "List 2013.10.xls" Next month it would be "List 2013.11.xls"

I came up with this formula but it is not working...can anyone tell me where I am going wrong?
INDEX("'"&CONCATENATE("C:\Files\Spreadsheets\",B1,".xls")!Items, Match($A$1,("'"&CONCATENATE("C:\Files\Spreadsheets\",B1,".xls")!Fruits,0))

In this, Cell B1 = "List 2013"

Can anyone help me here??

Thanks in advance

As far as I can tell from your formula, that's not what CONCATENATE is for.

Perhaps if you could post an example workbook it would make it easier to provide a solution.

Ive used this method with the PULL function before, so I know it can be done. I cant figure out how it can work with index and match though.

If you're using the value in a cell to reference a worksheet then you should look into using =INDIRECT()

Without an example worksheet, it's not clear what it is exactly you're trying to accomplish.

OK I attempted to upload 2 sheets but they propbably wont work since they link to each other. One sheet is called Spreadsheet1 1.xlsx, and the other is called Tenants.xlsx.

Tenants.xlsx is where call the information is kept, and will be the file that is referenced in Spreadsheet1.xlsx.

Spreadsheet 1:
Cell D3: Type of tenant I am looking for information about
Cell D4: Company Name associated with type of tenant
Cell D5: Contact Name for the company

The Cells in D4 and D5 link to another spreadsheet called "Tenant.xlsx" where the information is kept.
When I type in a type of tenant in D3, D4 and D5 will index and match the cells associated with that tenant.

So if D3=Office
D4 = corresponding Company Name for Office Tenant
D5 = corresponding Contact name for Office Tenant

If D3=Retail, same thing, just for retail tenant.

That all works great, and is no problem. What I want to happen is the following.
In the file called Spreadsheet 1, in Cell D1: I want to type the name of the corresponding excel file that the cells below will reference. So if:
D1 = Tenants
D4 and D5 should then link to an excel file called Tenants.xlsx

If D1 = Tenants 2013, D4 and D5 would then link to an excel file called Tenants 2013.xlsx.

Is that clearer?

Forgot to upload the 2 files, here they are.

Link below to exactly this issue. Hopefully it will prove useful. Certainly explains the limitations of what you're trying to do what with Excel needing the other workbook to be open to work..

http://www.excelforum.com/excel-form...-indirect.html

OK, I got halfway there using the following function:
INDEX(pull("'"&CONCATENATE("C:\Files\Spreadsheets\[",C23,".xlsx]Sheet1'!Chart")),down,over)

The weird thing I am having now is that the pull function works with a closed workbook, and the index function works with a closed workbook, but when I put them together it doesnt work with a closed workbook. Only works when it is open. Can anyone see a reason why it would do that?