Variable Workbook name

[Laithjo]

Hello Guys,
I'm facing an issue and i will be grateful if you can give me a hand.

I have an excel sheet that will be reading values from specific cells from other excel workbooks, the thing is that i need the file name (in the formula) to be variable, to make more sense please notice the below reference

='C:\Users\John\Desktop\Evaluation\Eval\[1876-192013.xlsx]Sheet1'!H37

I need this part (1876-192013.xlsx) to be variable according to the values of cells in the same sheet (somethimg like C6&"-"&D9), so it will fetch the data from the corresponding excel files with the same title. How can i do this? preferably without VB.

your help is highly appreciated,
Thank you,

[KOKOSEK]

Please Login or Register  to view this content.

[Laithjo]

Thank you verymuch for the response KOKOSEK,

But something weird happened, it correctly referenced the values of the cells in this sheet, but it somehow did not fetch the the value that is in the H37 cell in the other sheet.
it only results in the following
'C:\Users\John\Desktop\Evaluation\Eval\[1876-192013.xlsx]Sheet1'!H37
the cells i'm refrencing indeed have these values (1876 and 192013) but after this i need it to fetch the value in the H37 in the 1876-192013.xlsx file.

Thank you again for your efforts,
highly appreciated,

[Laithjo]

Any help guys, Pretty Please

[:) Sixthsense :)]

This question was asked so many times in this forum.

Making the cell value as reference then it requires Indirect() function.

Without using Indirect() function we can't make the cell value as reference.

But Indirect works with open workbook reference only. Indirect wont work with closed workbooks.

So, if you want to reference it to the closed workbook then you can use Indirect.Ext of MoreFunc addin which will work with closed workbook too.

Refer the below link to get Morefunc addin / usage.

http://www.ashishmathur.com/?s=morefunc