Using Vlookup with indirect function

[Deleted member 415602]

Hi guys,

I have a formula looking like this:
=VLOOKUP("Name",'C:\Users\mass\Desktop\stats[SCORECARD.xlsx]Sheet14'!$D$30:$K$30,5,FALSE)
This allow me to find the value adjacent to the cell containing Name in another workbook.
Now... I want to be able to write something like SheetA1' instead of Sheet14 in the formula. I only want to change the sheet number with another cell value.
I guess I'll have to use something with indirect() but I have no idea to do it.

Thank you for your help !

 

[ExcelEdge]

Greetings massoud15
You could try using the VLOOKUP function inside a nested IF function like this.

=IF(A1=1,VLOOKUP("Name",'C:\Users\mass\Desktop\[stats[SCORECARD.xlsx]Sheet14]stats[SCORECARD.xlsx]Sheet14'!$D$30:$K$30,5,FALSE),VLOOKUP("Name",'C:\Users\mass\Desktop\[stats[SCORECARD.xlsx]SheetA1]stats[SCORECARD.xlsx]SheetA1'!$D$30:$K$30,5,FALSE))

Do you need to VLOOKUP from 2 sheets or more?

 

[FDibbins]

INDIRECT only works on open files.. Maybe take a look at the Morefuc add-in which allows using INDIRECT on closed workbooks