Reference sheet name from cell

[mr_hiboy]

Hello all,

I'm trying to link to an external file, but using the data in a cell to reference the sheet name:

So this formula works, as it is referencing the file/sheet - the Product Sales.xls file has 24 sheets, one for each product.
=VLOOKUP($C$4,'[Product Sales.xls]Product A'!$A$14:$AQ$67,G$1,FALSE)

But here I have "Product A" in cell A1 and I want to update the formula so the file name is constant but the sheet name may change.

=VLOOKUP($C$4,'[Product Sales.xls]&$A$1&'!$A$14:$AQ$67,G$1,FALSE)


Any pointers would be great.

Thanks

 

[AIM]

@mr_hiboy

Please check if this helps:

=VLOOKUP($C$4,INDIRECT("'[Product Sales.xls]"&A2&"'!$A$14:$AQ$67"),G$1,FALSE)

Enter your sheet name in cell A2

Regards!!

 

[Luke M]

Hi Aim and mr_hiboy,
While AIM's formula looks like it should work, INDIRECT won't work with a closed external file. If the Product Sales workbook is open, then it works fine, but not a closed one.

If you are open to VB, you could setup a Change_Event macro that changes the formula based on what you put into A1.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
Application.EnableEvents = False
'Which cell gets the formula?
Range("B1").Formula = _
    "=VLOOKUP($C$4,'[Product Sales.xls]" & Range("A1").Value & "'!$A$14:$AQ$67,G$1,FALSE)"
Application.EnableEvents = True
End Sub