• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Reference sheet name from cell

mr_hiboy

Member
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
 
@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!!
 
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.
Code:
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
 
Back
Top