Automatically Update One Excel Worksheet from Another Sheet

Microsoft Excel is widely used to store and analyze all kinds of data. Interconnection between worksheets is an important feature. The connection must be set so that the data can be automatically updated as soon as the source file gets any change. Otherwise, the connection might not be that effective. There are eight ways you can do it smoothly to automatically update one Excel worksheet from another sheet. This article will guide you through these approaches as solutions. Additionally, you will find details on the functions that might be unfamiliar to you amidst the steps. All the methods have step-by-step instructions with a figurative description.

Contents hide

📁 Download Excel File

Learn to Automatically Update One Excel Worksheet from Another Sheet with These 8 Approaches

1. Using Paste Link

2. Utilizing Sheet Link Command

3. Implementing Keyboard Shortcut

4. Applying Workbook Link

5. Employing IF Function

6. Using Drop-Down Option

7. Applying Power Query Tool

8. Employing VLOOKUP Function

📝 Takeaways from This Article

Conclusion

📁 Download Excel File

Download the Excel file below.

Automatically Update One Excel Worksheet to Another.xlsx

Learn to Automatically Update One Excel Worksheet from Another Sheet with These 8 Approaches

The article is going to introduce eight methods to automatically update one Excel worksheet from another sheet. The methods will provide simple to complex solutions gradually. The simple solution involves Paste Link, Sheet link command, Keyboard shortcut, and Workbook link. On the other hand, complex ones will be covering the IF Function, List Drop-Down, the Power Query Tool, and the VLOOKUP function. Users will get this versatile walkthrough of these solutions to one of the fundamental problems.

Approach

1. Using Paste Link

This method is mostly used to automatically update one Excel worksheet from another sheet. Using the Context Menu or Paste Special box to choose the Paste Link option is convenient for all kinds of users.

⬇️⬇️ STEPS ⬇️⬇️

Firstly, select the cell range, that you want to keep updated on another worksheet.
Copy it using Ctrl+C.

Secondly, go to the new worksheet and choose a cell to paste on.
From the Context Menu, choose Paste drop-down, then Paste Link, under the Home tab like the image provided below.

As shown in the image, “Worksheet Update” is the source sheet. Following this method, you have selected an entire region and linked it to another worksheet named “Paste Link”.

Approach

2. Utilizing Sheet Link Command

A click on a different worksheet, while working on another one may get you the sheet linking feature of Excel. You can do it by writing the command manually as well.

⬇️⬇️ STEPS ⬇️⬇️

To begin with, select a cell on the result worksheet named “Sheet Link”.
Next, type “=” on it.
Now, click on the source worksheet “Worksheet Update”.

After that, you have been redirected to the source worksheet.
Carefully, select the same cell that you want to link to the source.
When you get the following formula, press Enter.

=’Worksheet Update’!B6

You will find your data linked and pasted to the cell automatically.

Use the Fill Handle icon to copy the formula to every required cell under the table.

Approach

3. Implementing Keyboard Shortcut

This method depends on the sheet link as well. But it is modified by keyboard shortcuts, rather than mouse clicks or manual writing.

⬇️⬇️ STEPS ⬇️⬇️

Initially, choose the cell on the current worksheet “Keyboard Shortcut”.
Next, type “=” on it.
Here, you need to go to the source worksheet, using Ctrl+pg up. You should use the Page Up (pg up) key as many times as your source worksheet is left of your current worksheet.

Choose the cell from the source.
Hit the Enter key.

You will find your data linked and pasted to the cell automatically.

Use the Fill Handle icon to copy the formula to every required cell under the table.

Approach

4. Applying Workbook Link

There is an easy method that can even link different workbooks. Using this method you can merge and update data from sources automatically.

⬇️⬇️ STEPS ⬇️⬇️

Select a cell that will have the linked data. Say B6.
Now, type “=” on it.

Keep the source workbook on the left as shown in the following image.
Next, click on the source cell twice. You will see the formula on your new workbook as “‘[Source Workbook Name]Worksheet Name’!$B$6”.
Press the Enter key.

You will find your data linked and pasted to the cell automatically.

Use the Fill Handle icon to copy the formula to every required cell under the table.

Approach

5. Employing IF Function

This method is specifically used to automatically update one excel worksheet from another sheet in terms of given conditions. For data analysis, this is most commonly used. In this method, the article will brief you on a case where the current worksheet will automatically update records of “Doctor” under the Profession column, whenever the source worksheet gets any new data.

⬇️⬇️ STEPS ⬇️⬇️

In the beginning, select a cell that will have the linked data. Say B6.
Now, type “=IF” on it. Select the IF function using the Tab key.
Click on the source worksheet “Worksheet Update”.

From the source, you can try the following condition to apply.

=IF(‘Worksheet Update’!$C7=“Doctor”,‘Worksheet Update’!B7,“”)

The formula will look for the Doctor under the C column. If found, it will update the data on the cell. If not found, the cell will remain blank due to using “”.
Hit the Enter key.

You will find your data linked and pasted to the cell automatically.

Finally, use Fill Handle to copy the formula to every required cell under the table.

Approach

6. Using Drop-Down Option

Here, you can run the chosen data under the source sheet. Another sheet will be automatically updated with the chosen data only. The source sheet needs to be under List format for this particular step.

⬇️⬇️ STEPS ⬇️⬇️

Let’s assume you have a list on E6, based on the data of the C column.
Choose one of the options. For example, the image shows the choice of Banker.

From the source worksheet “Drop-Down”, go to the new or current worksheet “New Drop-Down”.
Select C6 to get the chosen data only from the source.
Next, type “=” on it.
Click on the source worksheet.

Select the Drop-Down cell E6.
Press the Enter key.

You will find your data linked and pasted to the cell automatically.

Approach

7. Applying Power Query Tool

In case you need to automatically update one excel worksheet from another sheet, Power Query Tool is the perfect method for you. It presents your data in a table and shows it to you in any form you want. Therefore, this article will instruct you using the example for better understanding.

⬇️⬇️ STEPS ⬇️⬇️

Select the cell range from the source worksheet “Power Query”.
Choose the From Table/Range option from the Data tab.
A dialogue box Create Table will pop up. Confirm the range of your table.
Hit OK.

A new window for Power Query Editor will be available at this stage. You can see your table preview here.
Rename the table with “Query Result”.
Click on the icon over Close & Load.

Eventually, you will get a new worksheet with the table name “Query Result”. Here all your source data will be sorted and linked.

Approach

8. Employing VLOOKUP Function

The VLOOKUP function enables you to look for data under conditions. You can use it to update your new worksheet under a wide range of cells with your requirements. The description below is to find Email IDs followed by the Names from the source worksheet. It will automatically update upon data entry or editing.

⬇️⬇️ STEPS ⬇️⬇️

Initially, select a cell that will have the linked data. Say C6.
Now, type “=VLOOKUP” on it. Select the VLOOKUP function using the Tab key.
Since we are looking for Name from the “Worksheet Update”, lookup_value should be B6.
Click on the source worksheet “Worksheet Update”.

To set the table_array, you need to choose the entire table from the source worksheet now.
After the table choice, type 3 as the 3^rd column (counting from the 1^st column) Email ID has the data that you want to update on your worksheet.
Next, finish the formula with 0.
It should be similar to the following formula.

=VLOOKUP(B6,‘Worksheet Update’B6:D8,3,0)

Hit the Enter key.

Cell C6 will get the required data as shown in the image below.

Finally, use the Fill Handle icon to copy the formula to every required cell under the table.

📝 Takeaways from This Article

The article allows the readers to understand the variety of options available to automatically update one excel worksheet from another sheet.

📌 Using Paste options is a common practice. It starts with copying with Ctrl+C and ends with choosing Paste Link.

📌 Link your source sheet with the new sheet to get a direct update for data. You can click the source or type down the source Sheet link formula.

📌 You can also link your sheet using Ctrl+Pg Up and select cell ID to update.

📌 To link multiple workbooks (i.e., multiple Excel files) you can click on the source cell of the previous workbook. But the click needs to be twice to confirm the link.

📌 IF function is useful for conditional copying or filtering of data. It is updated just as the source data instantly.

📌 In case of listed data, that you need to choose one at a time, the Drop–Down option is perfect. When you link your listed cell, your data will automatically update from one excel worksheet to another sheet.

📌 Power Query Tool is the most advanced and effective method. It can automatically update data to one Excel worksheet from another sheet.

📌 VLOOKUP method is seemingly one of the most complicated methods. It is the only method that gives you the choice to look for data and update it as per need on another worksheet.

Conclusion

To automatically update one excel worksheet from another sheet, the Paste Link option, the sheet linking feature, keyboard shortcuts Ctrl+pg up, double click for workbook link, the IF function, List Drop–Down, the Power Query Tool, and VLOOKUP function can be used as per the article. In case of further queries, readers are requested to leave a comment for the author. The author will try their best to come up with a suitable solution. Follow ExcelDen to get more access to solutions regarding your Excel problems.