Naimul Hasan Arif
Our workloads can be easily reduced if we can link Excel sheets to another sheets inspite of doing the same things again and again. In this article, I’m going to explain how to link Excel sheets to another sheet.
For more clarification, here I am going to use a Dataset of Revenue of “EFH” Company. The Company’s revenue from selling Fruits in Germany is shown below:
Again, the Company’s Revenue on selling Fruits in Spain is shown below:
Now, we have to calculate the total Revenue in Germany and Spain from the previous datasets.
Download Practice Workbook
5 Ways to Link Excel Sheets to Another Sheet
1. Selecting the Cells to Link Sheets to Another Sheet
We can select the cells of sheets to link to another sheet.
Steps:
- Select a cell where you want to link sheets. Here, I select the C5 cell.
- Input a function where you want to utilize the links. I chose SUM Function where I want to link from Dataset2.
- Then, go to the Sheet and select the cells. Here, I went to the Sheet named Dataset2 and selected cells C5:C9.
=SUM(Dataset1!C5:C9)The SUM function will add the values in cells C5:C9 in the Dataset.
- Hit ENTER.
We can do the summation here.
Similarly, we have done this in cell C6 where I chose data from cells C5:C9 of Dataset1.
Read More: How to Link Cell to Another Sheet in Excel (7 Methods)
2. Applying Define Name to Link Sheets to Another Sheet
Define Name is an amazing feature to link sheets to another sheet.
Steps:
- Select the cells to define a name. Here, I selected cells C5:C9.
- Then, go to the Formulas tab and click on Define Name.
A dialogue box will appear.
- Give a suitable name and define the Range. I chose the name Spain and selected Range C5:C9.
- Next, press F4 3 times to vanish the Dollar Signs ($).
- Hit OK.
- Then, choose a cell where you want to call the defined name. Here, I chose the defined name Spain in the SUM Function in cell C5.
- Press ENTER.
Then, we will have the desired result.
Similarly, I have defined another name Germany to calculate summation in cell C6.
Read More: How to Link Sheets in Excel with a Formula (4 Methods)
Similar Readings
- How to Link Multiple Cells from Another Worksheet in Excel (5 Easy Ways)
- Link Excel Workbooks for Automatic Update (5 Methods)
- How to Link Files in Excel (5 Different Approaches)
- Link Word Document to Excel (2 Easy Methods)
- How to Link Excel Workbooks (4 Effective Methods)
3. Employing Paste Link to Link Sheets to Another Sheet
Paste link is another very easy and effective way to link sheets to another sheet.
Steps:
- Select the cells that we want to link with another sheet. Here, I selected Cells C5:C9 in dataset2.
- Right-click on the mouse and select Copy.
We can also press CTRL + C to copy.
- Then go to the desired cell of the worksheet where you want to link. Here I selected cell B5 of the Paste Link sheet.
- Right-click on the mouse and select Paste Link.
The cells will be linked.
Similarly, I have linked with the data of Dataset1 in cell C5.
Read More: How to Link Sheets to a Master Sheet in Excel (5 Ways)
4. Using Hyperlink to Link Excel Sheets to Another Sheet
The Hyperlink is another option to link sheets to another sheet.
Steps:
- Select the cell that you want to Hyperlink. I picked cell C4 for that purpose.
- Then, right-click on the mouse.
- Go to Link and select Insert Link.
A dialogue box will appear.
- Now, pick Place in This Document.
- Again, fill the text to display, Type the cell reference. Here, I chose Germany as a display text of Hyperlink. I also chose Dataset1 Worksheet and C4 as a Reference cell.
- Click on OK and the word will be hyperlinked.
- Now, click on the Hyperlinked word and it will take you to the Reference Sheet.
As soon as I clicked my Hyperlinked word Germany, it took me to the Reference Sheet Dataset1.
Read More: How to reference cell in another Excel sheet based on cell value!
5. Applying INDIRECT Formula to Link Sheets to Another Sheet
INDIRECT Function can link sheets to another sheet quite easily.
Steps:
- Select a cell and input the INDIRECT Function. Here, I have selected cell C5 and input the formula.
=INDIRECT("'"&B5&"'!C5")
Here, I used B5 as a reference text of the Sheet Name and used C5 cell as the destination.
- Press ENTER.
We can see the output on the selected cell.
Similarly, we can do it for cell C6. You also can use the Fill Handle to AutoFill formula for the rest of the cells.
Read More: How to Reference Worksheet Name in Formula in Excel (3 Easy Ways)
Practice Section
Using the values in Dataset1 and Dataset2, you can practice here for further expertise.
Conclusion
I have tried to elaborate as simply as possible to show how to link Excel sheets to another sheet. I hope it will be helpful for Excel users. For any more questions, comment below.
