How to Update One Excel Worksheet from Another Sheet Automatically

Provided that you have prepared a worksheet on your workbook and you want to utilize it in another sheet. In this situation, you can unconsciously use a copy-pasting tool. But it will not update automatically if any change happens in the main sheet or first sheet. In that case, what should you do?

In this article, I’ll demonstrate how you can automatically update one excel worksheet from another sheet.

Download the Practice Workbook

How to Automatically Update One Excel Worksheet from Another Sheet

1. Paste Link Option

In the right corner of the Paste Options, the Paste Link option is available. Using this you can copy any value as a link to the main cell. Let’s apply like the following

Steps:

• Select the cell or cell range that you want to build any link. Here, I selected the whole dataset. You can even select one cell depending on your demand

• Now pick any blank cell or cells
• Right-click on the mouse button and you will see the Paste Link option and click on the option. You’ll get the following.
• In the following figure, cell B2 of sheet 2 presents the link data of cell B2 of Sheet 1.
• If you change the data in sheet 1, it will be automatically updated on sheet 2.

2. Updating Worksheets Using Exclamation Mark (!) (Link Formula)

Using exclamation mark (!) is the most popular method for automatically updating sheets. In this method, the main syntax is

Let’s update our working sheet using this method.

In the following figure, we see a dataset of  Warehouse which is mainly a collection of Shop 1 and Shop 2. If we change any data in Shop 1 or Shop 2, data from the Warehouse sheet will be automatically updated.

Steps :

• Select the blank cell of C3 of Shop 1
• Type Equal sign (=) and click the sheet name of Warehouse
• Type the Exclamation Mark (!)
• Also, click your desired cell (e.g. C3 of Warehouse datasheet)

Like Shop 1, you can easily link the data of Shop 2 sheet with the main Warehouse sheet

Now, if you make any change in the working sheets Shop 1 & Shop 2, Warehouse working sheet will be automatically updated.

3. Updating Worksheet by Writing Link Formula Manually

This method has no specialty from using Exclamation Mark except writing the formula manually! If you don’t want to click on working sheet and cell range, then adopt this method where you have to write the Sheet name and cell range manually just like this

Steps:

• Select any blank cell Type the Equal Sign (=)
• Write the Sheet Name and Exclamation mark (!)
• Also, write the cell reference

4. Updating Worksheets Based on Criteria

Sometimes you may have to update data from one sheet to another sheet based on specific criteria. In those cases, you can apply the following method

It’s actually an application of the IF function. The syntax of the IF function is

Right now, you can employ the IF function like the following

Steps:

• Select a blank cell
• Type the IF formula like =IF(Warehouse!C4>100, Warehouse!C4, ""). Here, Warehouse our main sheet and C4 is the blank cell and we want the value of mango which is greater than 100

• Now we get the value of Mango as it is greater than 100.

Things to Keep in Mind

Be careful about the file name, file location, and file extension. If you don’t know the exact name of the excel file, you’ll not be able to find the desired file. More importantly, be cautious about the file location. You’ll receive the following message if you forget the location.

Besides, the extension of the Excel file is .xlsx. Be aware of this also.

Conclusion

Now you have these methods to update automatically one excel working sheet from another working sheet. Hopefully, it might be helpful for you. If you have any thoughts, please share.

Further Readings

• Using Excel to get data from another sheet based on cell value