# How to Link Sheets in Excel? (Same and Different Workbooks)

In the following overview image, clicking on any cell of the Link column will redirect to the corresponding sheets.

• Select cell C5.
• Apply the following formula and press Enter.

• Drag the Fill Handle to cell C7.
• Click on C5 and you’ll go to the New York sheet.

### Method 2 – Link Sheets Using a Cell Reference in a Formula

• Choose cell C5.
• Apply the formula below and press Enter.

='New York'!F13

• Drag the Fill Handle to C7.

• If we change the cell F13 value in sheet New York, the value of cell C5 will be updated.

### Method 3 – Use the Name Box to Link Sheets in Excel

• Select cell F13 in the New York sheet.
• Give a name to cell F13.

• Name the F13 cells in the Boston and Los Angeles sheets.

Click the image to get a better view

• Select cell C5 and apply the formula below.

=NY_Total_Sale

• Choose cell C6 and input the following formula.

=BT_Total_Sale

• Go to cell C7, apply the equation below, and press Enter.

=LA_Total_Sale

• Select cell C5.
• Go to Insert and click on Link.

The Edit Hyperlink window will appear.

• Choose Place in This Document.
• Type in the display text and input the cell reference.
• Select New York from the sheet list and press OK.

• Follow the same procedure for cells C6 and C7.

• If you click on cell C7, the Los Angeles sheet will be active.

### Method 5 – Run Excel VBA Code to Link Sheets

• Choose the sheet you want to make the links in.
• Press Alt + F11 to open the VBA editor.

• Click on Insert then on Module.
• Insert the following code and press F9 to run.

Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
Dim cell As Range
Dim sheetName As String

Set wb = ThisWorkbook
Set rng = wb.Sheets("Excel VBA Internal").Range("C5:C7")

For Each cell In rng

sheetName = cell.Value
Set ws = wb.Sheets(sheetName)

If Not ws Is Nothing Then
End If

Next cell

End Sub

• If you click on cell C5, the New York sheet will activate.

## How to Link Sheets in Different Workbooks in Excel

### Method 1 – Use Excel Formulas to Link Sheets in Different Workbooks

• Open the Source Workbook.
• Select cell C5 of the destination workbook and apply the formula below.
=INDIRECT(CONCATENATE("'[Source Workbook.xlsx]",B5,"'!\$F\$13"))

• Drag the Fill Handle to cell C7.

• If we make any changes to the Source Workbook, values in the current workbook will be updated.

Notes
You may get a security alert saying, “Automatic update of links has been disabled” with an Enable Content button when reopening the destination file. If you want to update the destination file whenever the source data is modified, click Enable Content.

### Method 2 – Link Sheets in Different Workbooks by Running VBA Code

• Navigate to the Developer tab.
• Click on Visual Basic.

The VBA Editor window will appear.

• Click on Insert and select Module.
• Insert the following code and press F9 to run.

Dim sourceWorkbook As Workbook
Dim targetWorkbook As Workbook
Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim targetRange As Range
Dim cell As Range
Dim sheetName As String
Dim sourceFilePath As Variant

With Application.FileDialog(msoFileDialogFilePicker)
.Title = "Select Source Workbook"
.Filters.Clear
.Filters.Add "Excel Files", "*.xls; *.xlsx; *.xlsm"
.AllowMultiSelect = False

If .Show = -1 Then
sourceFilePath = .SelectedItems(1)
Else
Exit Sub
End If
End With

Set sourceWorkbook = Workbooks.Open(sourceFilePath)
Set targetWorkbook = ThisWorkbook
Set targetSheet = targetWorkbook.Sheets("Excel VBA External")
Set targetRange = targetSheet.Range("C5:C7")

For Each cell In targetRange
sheetName = cell.Value
Set sourceSheet = sourceWorkbook.Sheets(sheetName)
If Not sourceSheet Is Nothing Then
End If
Next cell

ThisWorkbook.Activate

End Sub

A dialog box will open up.

• Choose Source Workbook.xlsx and press OK.

• If you click in cell C5 in the current workbook, the New York sheet in the Source Workbook will activate.

## Potential Issues with Linking Sheets

Linking large amounts of data between sheets may decrease workbook performance. We recommend importing data if you are working with a large dataset.

## Things to Keep in Mind

• Keep the workbooks open when going through each method.
• When applying Excel VBA to link sheets in another workbook, ensure it is not open.
• If you want to apply the first method to link externally, you must open the Source Workbook first.

### What is meant by a linking sheet?

A linking sheet refers to establishing connections between different worksheets in Excel. As a result, data can be shared and updated automatically between them.

### How do I link data from Sheet1 to Sheet2 in Excel?

In Excel, you can use formulas to link data from Sheet1 to Sheet2. In Sheet2, select the cell where you want the linked data to appear. Next, type an equal sign (=) followed by Sheet1, an exclamation mark (!), and the cell reference in Sheet1.

### How do I automatically import data from one sheet to another?

Go to the sheet where you want to import the data. Go to the Data tab, select Get Data, and choose From Other Sources.

## Excel Link Sheets: Knowledge Hub

<< Go Back To Linking in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto, BSc, Information Technology, Jahangirnagar University, Bangladesh, has worked with the ExcelDemy project for over a year. He has written 50+ articles and provided solutions of 100+ comments for ExcelDemy. Currently, he works as an Excel & VBA Developer and provides support and solutions in the ExcelDemy Forum. He has solved 100+ ExcelDemy Forum problems. His work and learning interests are in developing various Excel & VBA and Desktop applications. Outside of work, he enjoys Chess... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF