?How to GoTo from list to a worksheet with workbook?
?How to GoTo from list to a worksheet with workbook?
(OP)
I posted this in a different forum, (Thanks IRstuff) so let's see if how you folks can help. I've been away from spreadsheet building for too long I guess.
I can't find it in my Excel Help section - I guess MS is getting cheaper with the assistance.
I've created a workbook of customers, cell A1 is the account name in all sheets. If I have a list of customers in column A of the first worksheet, how can I click on the customer I want and have it go to that particular tab or worksheet?
I'm running XP on a laptop, I think Excel is Win2000 version
Thanks in advance.
I can't find it in my Excel Help section - I guess MS is getting cheaper with the assistance.
I've created a workbook of customers, cell A1 is the account name in all sheets. If I have a list of customers in column A of the first worksheet, how can I click on the customer I want and have it go to that particular tab or worksheet?
I'm running XP on a laptop, I think Excel is Win2000 version
Thanks in advance.
There is a theory which states that if ever anybody discovers exactly what the Universe is for and why it is here, it will instantly disappear and be replaced by something even more bizarre and inexplicable. There is another theory which states that this has already happened. -- Douglas Adams





RE: ?How to GoTo from list to a worksheet with workbook?
> Create a hyperlink in the cell to the sheet required. Unfortunately, renaming the target sheet will break the link.
> Brute force macro
Public Sub Jump()
Target = ActiveCell.Value
Sheets(Target).Select
End Sub
You can assign that to a button. Select the correct cell, click the button or run the macro
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: ?How to GoTo from list to a worksheet with workbook?
1. User clicks any cell in column A
2. Excel switches to to the worksheet that has the same value in cell A1 as the clicked cell, regardless of sheet name.
The only way to do exactly this would be to program it into the worksheet's "SelectionChange" event. However, if you did that you would never be able to edit the data in the main sheet's Column A. However, minimal functionality would be lost if it were programmed to the BeforeDoubleClick event.
Go into the VBA editor (Alt-F11 is quickest) and double-click on the name of the first worksheet on the left-hand tree. It should bring up a blank window or area with two dropdowns at the top containing "(General)" and "(Declarations)". Paste the code below into that blank area. Then try double-clicking on a cell in Column A of your client list sheet.
CODE
Dim sMsg As String
Dim FindString As String
Dim FoundSheets As New Collection
Dim i As Long
If Target.Column <> 1 Then
Exit Sub
End If
FindString = Target.Text
If FindString = "" Then
Exit Sub
End If
Cancel = True
For i = 1 To Worksheets.Count
If Not (Worksheets(i) Is Application.ActiveSheet) Then
If StrComp(FindString, Worksheets(i).Cells(1, 1).Text, vbTextCompare) = 0 Then
FoundSheets.Add Worksheets(i)
End If
End If
Next i
If FoundSheets.Count = 0 Then
MsgBox "No sheet found with """ & FindString & "."""
ElseIf FoundSheets.Count = 1 Then
FoundSheets(1).Activate
Else
sMsg = FoundSheets.Count & " sheets were found with """ _
& FindString & ".""" & vbCrLf & vbCrLf
For i = 1 To FoundSheets.Count
sMsg = sMsg & i & ". " & FoundSheets(i).Name & vbCrLf
Next i
sMsg = sMsg & vbCrLf & "Please enter the number of the desired sheet:"
On Error GoTo QUITME
i = CLng(InputBox(sMsg, "Choose Sheet", "1"))
FoundSheets(i).Activate
End If
Exit Sub
QUITME:
MsgBox "Invalid input. Please try again"
End Sub
-handleman, CSWP (The new, easy test)
RE: ?How to GoTo from list to a worksheet with workbook?
For example if all the sheets had cell (B2 say) with a formula containing "Customer" & A1 then searching for Customer would list the sheets along with the customer name. No VBA required
Of course what you are trying to do should really be done with a database!
RE: ?How to GoTo from list to a worksheet with workbook?
There is a theory which states that if ever anybody discovers exactly what the Universe is for and why it is here, it will instantly disappear and be replaced by something even more bizarre and inexplicable. There is another theory which states that this has already happened. -- Douglas Adams