Pass worksheet name to Vlookup function in Excel or VB

I would like to know if there is a way to pass a worksheet name to a VLookup function in Excel or using VB code.  

=VLOOKUP(INDIRECT(Worksheet_Name!$C$1),Worksheet_Name!INFOTAB,1,FALSE)

moriniaAdvanced Analytics AnalystAsked:
Who is Participating?
 
Rory ArchibaldConnect With a Mentor Commented:
=VLOOKUP(INDIRECT(INDIRECT("R1C3",0)),INDIRECT("INFOTAB"),1,FALSE)
unless INFOTAB is a dynamic named range?
0
 
Patrick MatthewsCommented:
Can you take another crack at explaining your question, and perhaps illustrate it with a sample file?
0
 
CluskittCommented:
I think he means something like:

=VLOOKUP(INDIRECT(<ValueOfCellA1Here>!$C$1),Worksheet_Name!INFOTAB,1,FALSE)

Dunno if you can do it by excel, but by VBA it's easy:

Cells(1,1).Formula="=VLOOKUP(INDIRECT(" & strSheetVar & "!$C$1),Worksheet_Name!INFOTAB,1,FALSE)"

This writes to cell A1 and looks up in whatever sheet you have in your variable.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
wobbledCommented:
This is how you get the worksheet name in VBA

Public Function WkShtName() As String
   
    WkShtName = ActiveSheet.Name

End Function

By making it a public function you can directly refer to it from the Excel Sheet eg =WkShtName
0
 
CluskittCommented:
Err... replace the second worksheet_name as well:

Cells(1,1).Formula="=VLOOKUP(INDIRECT(" & strSheetVar & "!$C$1)," & strSheetVar & "!INFOTAB,1,FALSE)"
0
 
MWGainesJRCommented:
this formula will return the sheetname: (http://www.exceltip.com/st/Cell_Function_Returns_Sheet_Name,_Workbook_Name_and_Path/180.html)

=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
so in your case,  you formula would be:

 =VLOOKUP(INDIRECT(MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)!$C$1),MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)!INFOTAB,1,FALSE)

0
 
moriniaAdvanced Analytics AnalystAuthor Commented:
The following statement is a named field in Excel.

=VLOOKUP(INDIRECT(Total_Authorizations!$C$1),Total_Authorizations!INFOTAB,1,FALSE

If possible, I would like to use the same named field for four tables.  The problem I am having is that the table name automatically is put in when I save the formula as shown above.  When I create the formula it looks like this

=VLOOKUP(INDIRECT($C$1),INFOTAB,1,FALSE

Upon saving it adds the Table Name.  Unfortunately this gives erronous results when I try to use it on a different worksheet.  However, when on that worksheet.

Because all files are made public, I cannot post the file.

I am willing to change the code to VB and create a user defined function if that is the only way.
0
 
MWGainesJRCommented:
morinia,
I gave you the formula need above without using VBA.
0
 
Patrick MatthewsCommented:
Using the CELL function, as per MWGainesJR above, works, but only as long as the file has already been saved; try entering that before the workbook has been saved, and you get a #VALUE error.

However, as far as I know, the CELL function is the only non-VBA way to get at this.

If you go the UDF route, I strongly recommend *not* keying on the ActiveSheet.  For example, I would be more inclined to use something like the following:




Public Function WkShtName(Optional Rng As Range) As String
   
    If Rng Is Nothing Then
        WkShtName = Application.Caller.Parent.Name
    Else
        WkShtName = Rng.Parent.Name
    End If

End Function



If you pass in a range reference, the function returns the worksheet name based on the parent of that range reference.  If you omit the range, the function returns the name of the parent of the cell containing the formula.
0
 
Patrick MatthewsCommented:
>>Because all files are made public, I cannot post the file.

Then post a file with an identical structure, but with "fake" data.  Not hard to do, really.
0
 
MWGainesJRCommented:
If the workbook isn't saved, I highly doubt it will have modules much less vba code
Unless of course you're gonna go all the way down the route of creating an xla or import the modules before attempting the lookup....
=)
0
 
Patrick MatthewsCommented:
MW,

>>If the workbook isn't saved, I highly doubt it will have modules much less vba code

You've never created a new workbook, and then added code before saving it?

:)

In any event, no criticism of your method was implied--not intentionally, at least.  It is probably the only non-VBA approach available.

Patrick
0
 
MWGainesJRCommented:
If the author is ok with adding code to every new workbook in order to do a vlookup rather than clicking "save", then by all means.....give'm the vba =)
0
 
moriniaAdvanced Analytics AnalystAuthor Commented:
matthewspatrick:

The code you gave returns the sheet name, how do I bring it into my formula.

=VLOOKUP(INDIRECT($C$1),INFOTAB,1,FALSE)
0
 
moriniaAdvanced Analytics AnalystAuthor Commented:
rorya

The formula worked perfectly.  Can you explain what the formula is doing for all of us.
0
 
Rory ArchibaldCommented:
It replaces your two range references that are being converted with INDIRECT so that you can use strings (no conversion).
It wouldn't be my first choice though.
0
 
moriniaAdvanced Analytics AnalystAuthor Commented:
What would be your first choice?
0
 
Rory ArchibaldCommented:
Something that doesn't involve INDIRECT or VLOOKUP, most likely.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.