In Excel copying data from one worksheet to another is an easy task but it does not have any link between the two. But we can create a link between two worksheets or workbooks to automatically update data in another sheet if it changes in first worksheet.
In Excel we can link worksheets and update data automatically. A link is a dynamic formula that pulls data from a cell of one worksheet and automatically updates that data to another worksheet. These linking worksheets can be in a same workbook or in another workbook. One worksheet is called source worksheet, from where this link pulls the data automatically, and other worksheet is called destination worksheet that contains that link formula and where data is updated automatically.
Remember one thing that formatting of cells of source worksheet and destination worksheet should be same otherwise result could be viewed differently and can lead to confusion.
We can link these two worksheets by following three methods and update data in various scenarios in Excel.
- Copy and Paste Link
- ➢ From source worksheet, select the cell that contain data or that you want to link to another worksheet, and copy it by pressing Copy button from Home tab or press CTRL+C.
- ➢ Go to destination worksheet and click the cell where you want to link the cell from source worksheet. On Home tab, click on drop down arrow button of Paste, and select Paste Link from “Other Paste Options”. Or right click in cell on destination worksheet and choose Paste Link from Paste Options.
- ➢ Save the work or return to source workbook and press ESC button on keyboard to remove the border around copied cell and save the work.
- Enter formula manually
- ➢ In the destination worksheet, click on cell that will contain link formula and enter an equal sign (=)
- ➢ Go to source sheet and click on cell that contains data and press Enter on keyboard. Save your work.
Using these two methods we can link worksheet and update data automatically depending upon requirement. In this article we will discuss some examples using following cases.
Update cell A1 on Sheet2 based on cell A1 on Sheet1 using formula =Sheet1!A1
Suppose we have a value of 200 in cell A1 on Sheet1 and want to update cell A1 on Sheet2 using linking formula, then we can do that by using any of the methods as mentioned above.
Using Copy and Paste Link method
Copy the cell value of 200 from cell A1 on Sheet1.
Go to Sheet2, click in cell A1 and click on drop down arrow of Paste button on Home tab and select Paste Link button. It will generate a link by automatically entering formula
Or right click in cell on destination worksheet, Sheet2, and choose Paste Link from Paste Options: It will generate linking formula automatically.
Entering formula manually
We can enter the linking formula manually in cell A1 on destination worksheet Sheet2 to update data by pulling it from cell A1 of Sheet1.
In cell A1 on Sheet2, manually enter equal sign (=) and go to Sheet1 and click on cell A1 and press ENTER key on keyboard. Following linking formula will be update in destination sheet that will link cell A1 of both sheets.
Update cell Sheet2!A1 if Sheet1!A1 meets condition >200
By entering the linking formula manually we can update data in cell A1 of Sheet2 based on condition if cell value of A1 on Sheet1 is greater than 200. We can do that by entering this logical condition in IF function. If cell A1 on Sheet1 meets this condition then IF function returns the value in cell A1 on Sheet2 otherwise it will return blank cell.
Here is the formula to link the cells of both sheets based on condition. We will enter this formula manually in cell A1 of Sheet2
Update cell Sheet2!A1 based on a drop-down list in A1
Suppose we have a drop down list in cell A1 of Sheet1 and we can update cell A1 on Sheet2 by entering link formula in cell A1 on Sheet2.
In cell A1 on Sheet2 we will manually enter this linking formula to update data automatically based on cell value selected from drop down list.
Linking data in real data set is more complex and depends on your situation, you might need to use techniques other than those listed above. If you are in a rush and want your problem answered by an Excel expert, try our service. The experts are available to help you 24/7 at the link to the right. The first question is free.