Go Back

How to Automatically Update Data in Another Sheet in Excel

Read time: 34 minutes

In Excel, copying data from one worksheet to another is an easy task, but there is not 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 the first worksheet. This article explains how this is done.

Automatically data in another sheet 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 the same workbook or in another workbook.

One worksheet is called the source worksheet, from where this link pulls the data automatically, and the other worksheet is called the 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 the same otherwise the result could be viewed differently and can lead to confusion.

Two methods of linking data in different worksheets

We can link these two worksheets using two different methods. 

  1. Copy and Paste Link
    • From source worksheet, select the cell that contains data or that you want to link to another worksheet, and copy it by pressing the Copy button from the Home tab or press CTRL+C.
    • Go to the destination worksheet and click the cell where you want to link the cell from the source worksheet. On the Home tab, click on the drop-down arrow button of Paste, and select Paste Link from “Other Paste Options.” Or right-click in the cell on the destination worksheet and choose Paste Link from Paste Options.
    • Save the work or return to the source workbook and press ESC button on the keyboard to remove the border around the copied cell and save the work.

  2. Enter formula manually
    • In the destination worksheet, click on the cell that will contain link formula and enter an equal sign (=)
    • Go to the source sheet and click on the cell that contains data and press Enter on the keyboard. Save your work.

Using these two methods, we can link a worksheet and update data automatically depending upon your requirements. In this article, we will discuss some examples using the following cases.

Update cell on one worksheet based on a cell on another sheet

Suppose we have a value of 200 in cell A1 on Sheet1 and want to update cell A1 on Sheet2 using the linking formula. We can do that by using the same two methods we’ve covered.

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 the drop-down arrow of Paste button on the Home tab and select Paste Link button. It will generate a link by automatically entering the formula =Sheet1!A1.

Or right-click in the cell on the 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 the destination worksheet Sheet2 to update data by pulling it from cell A1 of Sheet1.

In cell A1 on Sheet2, manually enter an equal sign (=) and go to Sheet1 and click on cell A1 and press ENTER key on your keyboard. The following linking formula will be updated in destination sheet that will link cell A1 of both sheets.
=Sheet1!A1

Update cell on one sheet only if the first sheet meets a condition

By entering the linking formula manually, we can update data in cell A1 of Sheet2 based on a condition if the cell value of A1 on Sheet1 is greater than 200. We can do that by entering this logical condition in an 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 this condition. We will enter this formula manually in cell A1 of Sheet2
=IF(Sheet1!A1>200,Sheet1!A1,"")

Update cell on one sheet from another sheet with a drop-down list

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 the cell value selected from the drop-down list.
=Sheet1!A1

Linking data in a 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. The first question is free.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

Have multiple sheets with colored cells (with no data contained within them) that I need to be all grouped together on a single sheet. Basically, if I have 3 cells colored on one sheet and 2 colored cells on another, I'd like a third sheet to show all 5 of the colored cells and automatically update as new cells become colored/uncolored.
Solved by B. Q. in 26 mins
I am trying to create a Google Sheet (or Excel, if necessary) that tracks students' attendance at detention. I think it will require one sheet for junior high and one sheet for high school data entry: Each student's name - last and first separate, if possible, but using the name as the unique identifier, taking into account some students might have the same last name; date the detention was assigned, amount of assigned detention time ("1 day=45 minutes of time); and finally, I would love to show a report or running tally of detention time remaining. Another sheet is likely necessary for the actual data entry for "time served," (I would call it the Attendance sheet), wherein each student's name is entered with the date and amount of time served (in minutes). Because there are many "repeat offenders" and it takes a student usually more than one attendance to serve the assigned detention time, this Attendance sheet is the point of data entry from which the report sheets (JH or SH) will draw out the time served and that time will be subtracted from the original assigned detention time, reflecting a "time remaining" column. Problem: Is it possible to rewrite the formula I am trying to use in Columns H+ so that the "unique identifier" (Last Name) can determine differences between students with shared last names and be easily filled to right for each new student that is assigned detention. Right now, students with the same last name cause issues, and I can't get it to properly "fill right" and automatically update the $A$# reference and creating a column for new students takes much longer than it should.
Solved by K. J. in 25 mins
I have a shape in one sheet that is hyperlinked to a list of names on another sheet. Before I click on the shape, is there anyway to hover over it and get basic information before I click? Also, let's say that basic information includes the number of people listed, if I delete a person from the list, is there anyway to automatically update the basic information from the hover?
Solved by M. B. in 22 mins
I have a sheet that I am calculating food macros. I have all of my calculations done. One of my columns is the amount of food allowed. I have another area where I enter the calories allowed. I want to be able to change the calorie intake and have all of my "amount of food allowed" automatically update for me.
Solved by Z. Y. in 18 mins
I have a sheet that I am calculating food macros. I have all of my calculations done. One of my columns is the amount of food allowed. I have another area where I enter the calories allowed. I want to be able to change the calorie intake and have all of my "Amount" automatically update for me. When changing the calorie intake, I want my amounts of food that are allowed to auto populate for me
Solved by K. A. in 16 mins
I'm looking to have a few different sheets that automatically update based on information that is added or changed in the master sheet. One sheet I need sorted by supervisors, one sheet by the admin, and another sheet sorted by the month of the year (in order). I tried simply referencing the master sheet, but it doesn't seem like the data is lining up correctly.
Solved by C. J. in 14 mins
Need help creating an excel sheet that takes information from one column in another sheet to automatically update the corresponding row or information in the master sheet.
Solved by S. H. in 22 mins
Please i am having an excel sheet which is linked together. Am having the Headings as follows 'Stack, Version, Category, Crop Season etc.' but i do the entry on another sheets (let say sheet 1) and it appears on the other sheet automatically( sheet2 ). I want, when i update my version number the Category and Crop season should update automatically (and the Category we have light crop, small beans and Type 4 with a crop season as 2017/18,2015/16,2014/15 respectively). i want if i manually enter the stack ,version , category, crop season on one sheet (sheet1) it should automatically update in sheet 2 , relating to the version number and stack
Solved by F. W. in 14 mins
When an Excel data poinit reaches a certain value I would like that value to be "texted" or "SMS" to my cell phone provider so I can recieve the message. The spread sheet is automatically connected to another data source and updates automatically every second.
Solved by F. J. in 22 mins

2
Leave a Comment

avatar
1 Comment threads
1 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
2 Comment authors
RaghavSudha kumari Recent comment authors
newest oldest
Sudha kumari
Guest
Sudha kumari

After linking source & destination worksheet, if i add a new row or column in the source worksheet how to get auto update in the destination worksheet. Kindly reply

Raghav
Editor
Raghav

Thanks for you question! The experts who write these blogs are also online and available to help. Ask me your question here: http://www.excelchat.co and I’ll be happy to help out!