How can I use a cell value to select a sheet tab?
 

I want to be able to use values in column A (which match the names of the
worksheet tabs in my workbook), to select which sheet I look up (using
VLOOKUP) values in column B from (which contain values contained in one of
those sheets).

I remember I did this once before using INDIRECT or something similar but I
can't remember exactly how - can anyone help?

Cheers

How can I use a cell value to select a sheet tab?
 

Right click sheet tabview codecopy/paste this.
Now, if you have the name of the sheet tab typed into a cell and double
click on that cell you are taken to the tab.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Application.DisplayAlerts = False
Dim WantedSheet As String
WantedSheet = Trim(ActiveCell.Value)
If WantedSheet = "" Then Exit Sub
On Error Resume Next
If Sheets(WantedSheet) Is Nothing Then
' GetWorkbook ' calls another macro to do that
Else
Application.GoTo Sheets(WantedSheet).Range("a1")
End If
Application.DisplayAlerts = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Monkey-See, Monkey-Do" wrote
in message ...
I want to be able to use values in column A (which match the names of the
worksheet tabs in my workbook), to select which sheet I look up (using
VLOOKUP) values in column B from (which contain values contained in one of
those sheets).

I remember I did this once before using INDIRECT or something similar but
I
can't remember exactly how - can anyone help?

Cheers

How can I use a cell value to select a sheet tab?
 

Hi,

Is this what you mean

=VLOOKUP(B1,INDIRECT(A1&"!A1:B30"),2,FALSE)

Where a1 contaibs a sheet name.

Mike

"Monkey-See, Monkey-Do" wrote:

I want to be able to use values in column A (which match the names of the
worksheet tabs in my workbook), to select which sheet I look up (using
VLOOKUP) values in column B from (which contain values contained in one of
those sheets).

I remember I did this once before using INDIRECT or something similar but I
can't remember exactly how - can anyone help?

Cheers

How can I use a cell value to select a sheet tab?
 

You VLOOKUP formula will probably have something like this as the
second parameter:

INDIRECT("'"&A1&"'!A:B")

where A1 contains the sheet name and I've assumed you have a two
column table in columns A and B of the sheet where you are trying to
get data from.

Hope this helps.

Pete

On Oct 13, 1:34*pm, Monkey-See, Monkey-Do
wrote:
I want to be able to use values in column A (which match the names of the
worksheet tabs in my workbook), to select which sheet I look up (using
VLOOKUP) values in column B from (which contain values contained in one of
those sheets).

I remember I did this once before using INDIRECT or something similar but I
can't remember exactly how - can anyone help?

Cheers

How can I use a cell value to select a sheet tab?
 

I misunderstood "SELECT"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
Right click sheet tabview codecopy/paste this.
Now, if you have the name of the sheet tab typed into a cell and double
click on that cell you are taken to the tab.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Application.DisplayAlerts = False
Dim WantedSheet As String
WantedSheet = Trim(ActiveCell.Value)
If WantedSheet = "" Then Exit Sub
On Error Resume Next
If Sheets(WantedSheet) Is Nothing Then
' GetWorkbook ' calls another macro to do that
Else
Application.GoTo Sheets(WantedSheet).Range("a1")
End If
Application.DisplayAlerts = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Monkey-See, Monkey-Do"
wrote in message
...
I want to be able to use values in column A (which match the names of the
worksheet tabs in my workbook), to select which sheet I look up (using
VLOOKUP) values in column B from (which contain values contained in one
of
those sheets).

I remember I did this once before using INDIRECT or something similar but
I
can't remember exactly how - can anyone help?

Cheers

How can I use a cell value to select a sheet tab?
 

Thanks Pete,

Spot on!

Cheers from the UK

"Pete_UK" wrote:

You VLOOKUP formula will probably have something like this as the
second parameter:

INDIRECT("'"&A1&"'!A:B")

where A1 contains the sheet name and I've assumed you have a two
column table in columns A and B of the sheet where you are trying to
get data from.

Hope this helps.

Pete

On Oct 13, 1:34 pm, Monkey-See, Monkey-Do
wrote:
I want to be able to use values in column A (which match the names of the
worksheet tabs in my workbook), to select which sheet I look up (using
VLOOKUP) values in column B from (which contain values contained in one of
those sheets).

I remember I did this once before using INDIRECT or something similar but I
can't remember exactly how - can anyone help?

Cheers

How can I use a cell value to select a sheet tab?
 

You're welcome - thanks for feeding back.

Pete

On Oct 13, 2:29*pm, Monkey-See, Monkey-Do
wrote:
Thanks Pete,

Spot on!

Cheers from the UK