How to Auto Populate from Another Worksheet in Excel

While working with multiple sheets in Excel, it’s very usual to feel the necessity to auto-populate data from another worksheet. There are a number of easy and simple ways to meet this objective. In this article, you’ll learn how you can auto-populate data from another worksheet with proper steps and illustrations.


1. Linking Excel Worksheets to Auto Populate from Another Worksheet

In the following picture, Sheet1 is representing some specifications of a number of smartphone models.

Auto Populate by Linking Worksheets in Excel

And here is Sheet2 where only three columns from the first sheet have been extracted. The Price column has not been copied yet as we’ll show different methods here to pull out the price list from the first sheet. We have to maintain some rules that will auto-update the price column if any change is made in the corresponding column in the first sheet (Sheet1).

Auto Populate by Linking Worksheets in Excel

Now let’s have a look at how we can link between these two worksheets so that data in one worksheet (Sheet2) will be auto-populated based on another worksheet (Sheet1).

📌 Step 1:

➤ From Sheet1, select the range of cells (F5:F14) containing the prices of the smartphones.

➤ Press CTRL+C to copy the selected range of cells.

Auto Populate by Linking Worksheets in Excel

📌 Step 2:

➤ Go to Sheet2 now.

➤ Select the first output cell in the Price column.

➤ Right-click the button of your mouse and choose the Paste Link option as marked within a red square in the following screenshot.

Auto Populate by Linking Worksheets in Excel

The Price column is now complete with the extracted data from the first sheet (Sheet1). Now we’ll see how a change of data in the primary worksheet (Sheet1) auto-populates data in the second worksheet (Sheet2).

📌 Step 3:

➤ In Sheet1, change the price value of any smartphone model.

➤ Press Enter and go to Sheet2.

Auto Populate by Linking Worksheets in Excel

And you’ll find the updated price of the corresponding smartphone in Sheet2. This is how we can easily link between two or multiple worksheets to auto-populate.

Auto Populate by Linking Worksheets in Excel


2. Updating Data Automatically by Using Equal Sign to Refer Cell(s) from Another Worksheet

Now we’ll apply another method where we won’t have to copy and paste anything from one worksheet to another. Rather, we’ll use the cell reference(s) from another worksheet to auto-populate data.

📌 Step 1:

➤ In Sheet2, select Cell D5 and put an Equal (=) sign.

Auto Update Data by Using Equal Sign to Refer Cell(s) from Another Worksheet

📌 Step 2:

➤ Go to Sheet1.

➤ Select the range of cells (F5:F13) containing the prices of all smartphone models.

➤ Press Enter.

Auto Update Data by Using Equal Sign to Refer Cell(s) from Another Worksheet

Now in Sheet2, you’ll find an array of prices in Column D ranging from D5 to D14. If you change any data in the Price column in Sheet1, you’ll also see the updated price of the corresponding item in Sheet2 right away.

Auto Update Data by Using Equal Sign to Refer Cell(s) from Another Worksheet

Read More: How to Autofill a Column in Excel


3. Using INDEX-MATCH Formula to Auto Populate from Another Worksheet in Excel

We can also combine the INDEX and MATCH functions to auto-update data from one worksheet to another in Excel.

📌 Step 1:

➤ Select Cell D5 in Sheet2 and type the following formula:

=INDEX(Sheet1!$B$5:$F$14,MATCH(Sheet2!$C35,Sheet1!$C$5:$C$14,0),MATCH($D$4,Sheet1!$B$4:$F$4,0))

➤ Press Enter and you’ll get the first extracted price of the smartphone from Sheet1.

Use of INDEX-MATCH Formula to Auto Populate from Another Worksheet in Excel

📌 Step 2:

➤ Now use Fill Handle to autofill the rest of the cells in Column D.

After extracting the price list for all smartphones in Sheet2, you can now easily auto-update any price of the corresponding smartphone in Sheet2 based on a price change in Sheet1.

Use of INDEX-MATCH Formula to Auto Populate from Another Worksheet in Excel

Read More: Excel Formulas to Fill Down Sequence Numbers Skip Hidden Rows


Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.


Concluding Words

I hope all of these simple methods mentioned above will now help you to apply them in your Excel spreadsheets when necessary. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.


Related Articles


<< Go Back to Excel Autofill | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Nehad Ulfat
Nehad Ulfat

NEHAD ULFAT is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET but switched gears, working as a content developer.  In this role, he creates techy content all about Excel... Read Full Bio

14 Comments
  1. I have a “form” (Xcell sheet) that my customer fills out, and then I want to get that data to populate into the cells of my own Xcell workbook. The problem is that if I copy the “Form” into my Workbook, whatever links I had setup previously will not link to the new “Form”…even if I give the Form Tab the same name as the previous one….the links are broken and would have to be re-set.

    Do you know a way to allow these links to the new Data to be maintained?

    thank you for any ideas you may have!

    • Hi PAUL R HARTLEY! Thank you for your query.

      You can fix these links easily using the following process.
      Go to the Data tab >> Queries & Connections group >> Edit Links tool.
      Afterward, all the links used in this workbook will be shown to you in the Edit Links window. Select individual links and click on the Check Status button for each of them.
      If you see an Error: Source not found in any status, click on the Change Source button. Subsequently, browse your “form” Excel sheet >> click on the OK button >> click on the Close button of the Edit Links window.

      Regards,
      Tanjim Reza

  2. WHEN YOU GO TO PASTE YOU CAN CLICK PASTE SPECIAL AND THEN SCROLL TO THE BOTTOM TO PASTE SPECIAL IT WILL GIVE YOU A MENU TO CHOOSE EXACTLY WHAT YOU NEED PASTED. THERE IS A CHOICE FOR PASTE LINK. YOU CAN TRY THAT

  3. Good day

    I have a spreadsheet with employee information that have been awarded bursaries. They’re studying with different universities and these universities have vendor codes. I need to create individual payment requisitions using a template. How do I only change the employee number in the payment requisition and the form auto populates other fields relating to that particular employee?

    • Hello, MRS B!
      Can you please send me your excel file via email? ([email protected]), so that I can solve your problem!

      Right now I’m giving you a quick solution without the dataset. You can use Excel’s VLOOKUP function to have fields in the payment request form automatically fill in depending on the employee number.

      Here is a formula that uses the VLOOKUP function as an example:

      =VLOOKUP(employee number,employee table,2,FALSE)

      Here, “Employee number” refers to the cell where the employee number input is located, “Employee table” refers to the cell range containing the employee information table, which includes the employee number in the first column, and “2” refers to the column number in the table that contains the university information.

      You can change this formula to return different data. Once the relevant information has been obtained from the table, you can use it to fill in the essential fields on the payment request form by utilizing straightforward cell references or other procedures.

      Hope this will help you.
      Good Luck!

      Regards,
      Sabrina Ayon
      Author, ExcelDemy
      .

  4. Good day, Looking for some help.

    I have a list of tasks on sheet1 that fall on different dates. I’m for a formula or macro that will extract that info and plan it on sheet2 based on the dates.

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Apr 4, 2024 at 2:29 PM

      Hello Vito Casa

      Thanks for visiting our blog and sharing your questions. Sheet1 contains a list of tasks with corresponding dates. You want to extract this information and plan the tasks on Sheet2 based on the dates. To do so, you can develop multiple formulas using VLOOKUP, INDEX, MATCH, and XLOOKUP. You can also use IFERROR to handle errors.

      SOLUTION Overview:

      1. Using VLOOKUP and IFERROR Functions
      2. Using INDEX, MATCH and IFERROR Functions
      3. Using XLOOKUP Function

      NOTE: If you are a Microsoft 365 user, you will be able to use the XLOOKUP function.

      I hope the formulas mentioned will reach your goal. I have attached the solution workbook as well; good luck.

      DOWNLOAD SOLUTION WORKBOOK

      Regards
      Lutfor Rahman Shimanto
      ExcelDemy

      • Reply
        VITO CASALINUOVO Apr 9, 2024 at 9:42 PM

        Thank you, I appreciate your work. I just have one problem. I have multipole task that fall on the same day. Is there a way to capture all

        • Lutfor Rahman Shimanto
          Lutfor Rahman Shimanto Apr 16, 2024 at 4:43 PM

          Dear Vito Casalinuovo

          It is good to see you again. Yes! You can capture all the tasks that fall on the same day. To do so, use the IFERROR, TEXTJOIN and FILTER function:

          SOLUTION Overview:

          Follow these steps:

          1. Choose cell C3.
          2. Insert the following formula: =IFERROR(TEXTJOIN(", ",TRUE, FILTER(Sheet1!$C$3:$C$7, Sheet1!$B$3:$B$7=Sheet2!$B3)), "")
          3. Drag the Fill Handle icon to copy the formula down.

          I hope you have found the formula helpful. I am also attaching the solution workbook; good luck.

          DOWNLOAD SOLUTION WORKBOOK

          Regards
          Lutfor Rahman Shimanto
          ExcelDemy

  5. Reply
    VITO CASALINUOVO Apr 8, 2024 at 9:39 PM

    That looks great. I only have one issue. the formula doesn’t pick up multiple tasks set for a set date. i.e April 10th I have 3 tasks. Can the formula be adjusted?

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Apr 17, 2024 at 10:08 AM

      Dear Vito Casalinuovo

      Thanks for further clarifying your problem. Based on the requirement, I have come up with another solution, though the previous solution works perfectly on our end.

      Assuming you have a dataset like the following:

      You want to get all the tasks that fall on the same day. To achieve the goal, you can combine:

      1. IFERROR, TEXTJOIN and IF Functions (New)
        =IFERROR(TEXTJOIN(", ", TRUE, IF(Sheet1!$B$3:$B$11=Sheet2!$B3, Sheet1!$C$3:$C$11, "")), "")
      2. IFERROR, TEXTJOIN and FILTER Functions (Previous)
        =IFERROR(TEXTJOIN(", ", TRUE, FILTER(Sheet1!$C$3:$C$11, Sheet1!$B$3:$B$11=Sheet2!$B3)), "")

      I hope these formulas will help you to reach your goal. I have attached the solution workbook as well; good luck.

      DOWNLOAD SOLUTION WORKBOOK

      Regards
      Lutfor Rahman Shimanto
      ExcelDemy

  6. Good day, Sorry for the late response. I will give this a try and I will report back.

    • Hello Vito,

      Don’t be sorry Vito. Let us know your feedback, hopefully it will work.

      Regards
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo