Formula to refer to a file name based on value in criteria cell [column]

Hello All,


I have attached a set of 4 Files [let's say that they are stored in location C:\Files\]:
1. Main File.xlsx [this is where I am working on the formula]
2. Core S4.xlsx
3. Core Bev.xlsx
4. Finishing.xlsx

the formula needs to be added to "Q6", being:

=if(iserror(vlookup(A6,'C:\Files\["Column I".xlsx]All Data'!A:L,12,false)),"",vlookup(A6,'C:\Files\["Column I".xlsx]All Data'!A:L,12,false)

Therefore in the value from column "I" from Main File needs to determine what file to refer to. this will be the name of a file.

I do remember that I have once worked on something where a formula could determine what tab to go to based on the value of a certain cell.

Does anyone think it is doable? Has anyone of you have ever done something like this before?

I appreciate any help.

Thank you in advance

Simon

Here is some useful information on "Indirect"

http://www.contextures.com/xlFunctions05.html

Hello davesexcel,

I have read this article and have a clear enough understanding of the function.

Works perfectly to what I need it to do.

Thank you greatly.