Solved

Pass worksheet name to Vlookup function in Excel or VB

Posted on 2010-09-10
18
822 Views
Last Modified: 2013-11-26
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)

0
Comment
Question by:morinia
  • 4
  • 4
  • 4
  • +3
18 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33646632
Can you take another crack at explaining your question, and perhaps illustrate it with a sample file?
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 33646650
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
 
LVL 17

Expert Comment

by:wobbled
ID: 33646651
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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 18

Expert Comment

by:Cluskitt
ID: 33646657
Err... replace the second worksheet_name as well:

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

Expert Comment

by:MWGainesJR
ID: 33646690
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
 

Author Comment

by:morinia
ID: 33646718
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
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33646745
morinia,
I gave you the formula need above without using VBA.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33646769
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33646791
>>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
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33646816
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33647075
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
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33647119
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
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 33647300
=VLOOKUP(INDIRECT(INDIRECT("R1C3",0)),INDIRECT("INFOTAB"),1,FALSE)
unless INFOTAB is a dynamic named range?
0
 

Author Comment

by:morinia
ID: 33647397
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
 

Author Comment

by:morinia
ID: 33647456
rorya

The formula worked perfectly.  Can you explain what the formula is doing for all of us.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33647477
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
 

Author Comment

by:morinia
ID: 33647509
What would be your first choice?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33647541
Something that doesn't involve INDIRECT or VLOOKUP, most likely.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VBA Works in Excel 2010 Not 2016 Help! 5 22
Excel format formula for currency 15 24
formatting - number format 2 14
Data Copy 4 28
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question