Open a worksheet by refrencing a number in 1 cell

[HAD]

Is it opssible to write a formula that will link to open a tab without
clicking on a hyperlink.? For instance I will have 3 pages in my workbook. I
will be using a program that will scan a number into cell A1. Once the
number is in A1 there will be a matching page with that number and I want
that page to automatically open. So when 15556453 is entered in A1 it will
open page 15556453 and display what is on that sheet. I will have page 3
that refrences a differnt number 16987543, when this number is entered into
A1 on the first page it will then open 16987543. Is there a way to have a
function open sheet and display what is on it?

Thanks!

 

[Gord Dibben]

Not possible with a formula to activate another worksheet.

You could possibly do it with VBA event code but I don't know if scanning a
value into a cell triggers a change event

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Sheets(Target.Value).Activate
End If
End Sub

This is worksheet event code. Right-click on the sheet tab and "View Code"

Copy/paste the code into that module. Edit to suit then Alt + q to return
to Excel.


Gord Dibben MS Excel MVP

 

[Jacob Skaria]

Select the sheet tab which you want to work with. Right click the sheet tab
and click on 'View Code'. This will launch VBE. Paste the below code to the
right blank portion. Get back to to workbook and try out entering sheet names
in cell A1 of this sheet.

'the below code is to be copied to the sheet module
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" And Trim(Target.Text) <> "" Then
If SheetExists(Target.Text) Then Sheets(Target.Text).Activate
End If
End Sub

Function SheetExists(ShName As String) As Boolean
On Error Resume Next
Set varSheet = Sheets(ShName)
If Err.Number <> 0 Then SheetExists = False Else SheetExists = True
End Function
'upto here

If this post helps click Yes