Reference File Name from Cell Value

[Crescendo]

Hello All,
I really need your help- i am willing to find out a way in which reference file name for vlookup is from cell O6 from each worksheet.

=IF($C6="","",IFERROR(VLOOKUP($C6,'C:\Users\XYZ\Desktop\[Indirect(!$O$6).xls]DataSheet'!$B$14:$AB$488,9,FALSE),0))

please help me

really appreciate it

 

[FDibbins]

The entire reference needs to be inside teh INDIRECT(), some thing like ...
=IF($C6="","",IFERROR(VLOOKUP($C6,INDIRECT("'[C:\Users\XYZ\Desktop\"&$O$6&".xls]DataSheet'!$B$14:$AB$488"),9,FALSE),0))
(Hope I got the syntax right, but thats how it is needs to look)

 

[Crescendo]

The entire reference needs to be inside teh INDIRECT(), some thing like ...
=IF($C6="","",IFERROR(VLOOKUP($C6,INDIRECT("'[C:\Users\XYZ\Desktop\"&$O$6&".xls]DataSheet'!$B$14:$AB$488"),9,FALSE),0))
(Hope I got the syntax right, but thats how it is needs to look)

Thanks alot ford, one question
I want sheet name to be Variable,
So if i am on A2 putting this formula than it should refer to O6 for same sheet
A3 than its o6 so on and so forth

Can you please suggest if its something doable?

 

[FDibbins]

One thing I forgot to mention, is that INDIRECT only works on open workbooks. If your other files will be closed, you will need to try the Morefunc add-in.

For what you want (referencing a wheet name) try this. It kinda looked like you wanted to reference a file name - or part of 1...
=INDIRECT("'[file name.xlsm]"&A8&"'!A11") (This is how it needs to look, in its simplest form)
=IF($C6="","",IFERROR(VLOOKUP($C6,INDIRECT("'[path and file name.xlss]"&A8&"'!$B$14:$AB$488"),9,FALSE),0))
where A8 contains the sheet name and A11 contains the cell you want to reference

You may have noted that I did not bother to absolute the range refs, because they are actually text now