Md. Abdul Kader
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.
Similar Readings
- Copy a Worksheet in Excel (5 Smart Ways)
- How to Protect Excel Sheet from Copy-Paste (4 Effective Ways)
- Copy and Paste in Excel Using VBA (7 Methods)
- How to Copy a Cell in Excel Using Formula(7 Methods)
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.
Read more: Macro to Copy Data from One Workbook to Another Based on Criteria
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.
how do I update the formatting (i.e. colors / fonts / etc) to the 2nd sheet ?
Thanks- my colleague updates a spreadsheet daily. I have read only status. I would like to auto import update my sheet with the other. I only get read only access