in the formula the workbook name is given from another cell

[cvraman]

Hi Guys,
Sorry if the question is stupid.
Is it possible to get the workbookname - in the below formula - from another cell?
e.a.: the workbook name is in cell C1 and the formula is in D1

='C:\[workbookname.xlsx]Sheet1'!$A$10

Is it possible to do that the workbook name is given into the formula from another cell without vba?

thanks in advance
TamasDobos

 

[Eric W]

Try using INDIRECT. For example:

=INDIRECT("'C:\["&C1&"]Sheet1'!$A$10")

 

[cvraman]

hi Eric,
The below one is working if both workbook is open but will result in #ref! if the referenced wb is closed.
Do you have any idea?

B1:= workbookname.xlsx
C1:= Sheet1
D1:= A1

=INDIRECT("'[" & B1 & "]" & $C$1 & "'!" & $D$1)
or
=INDIRECT("'C:\[" & B1 & "]" & $C$1 & "'!" & $D$1)

The original one:
='C:\[workbookname.xlsx]Sheet1'!$A$1

thanks in advance

 

[Eric W]

You are correct, you can't use INDIRECT to read a closed workbook. You can use something like:

=INDEX('C:\[workbookname.xlsx]Sheet1'!$A$1$Z$100,A1,B1)

and you can reference anything from 'C:\[workbookname.xlsx]Sheet1'!$A$1$Z$100 by changing A1 and B1 whether it's open or closed.. But regrettably, you can't use INDIRECT to change the workbook or sheet names.

 

[cvraman]

Thank you very much Eric