How can I hyperlink different cells in one worksheet to other worksheets? If I click on a link I should be taken to a specific worksheet.
There are two ways to solve the problem: Use hyperlinks or VBA code. Both are effective.
In the ‘Master’ sheet you can enter the text like Income, Expense, etc and also rename the the worksheet tabs using easy to remember names.
- Now select the text in the worksheet
- Click on insert tab
- Click Hyperlink in the Links group
- Under the ‘Link to:’ option in the ‘Insert Hyperlink’ window that pops up select ‘Place in this document’
- Select the appropriate worksheet in ‘Or select a place in this document’and cell address under ‘Type the cell reference’
- Click OK
- A hyperlink is inserted
- Now click on the hyperlink to go to the relevant worksheet
- Using the same procedure as above place a back link to the master sheet as shown in the training video
- Right-click on the master sheet
- Select View Code
- In the workspace in the Microsoft Visual Basic window paste the following code
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Target.Column = 1 Then
Now when you click on any number against the subject in the master worksheet you are brought to the relevant worksheet.
- Now double-click on ‘this workbook’ on the left
- Place the following code here
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _
ByVal Target As Range, Cancel As Boolean)
The above code allows you to come back to your master worksheet on double-clicking anywhere inside the worksheet.
The above procedures can be used in many other practical situations. You can map each cell in the master worksheet to a room number or a stock and use it to get details, analyze data or perform calculations.
Watch the Excel training video below to learn how to navigate a large workbook with many worksheets in Microsoft Excel quickly: