How to Auto Populate from Another Worksheet in Excel

Method 1 – Linking Excel Worksheets to Auto Populate from Another Worksheet

Sheet1 contains some specifications of smartphone models.

Auto Populate by Linking Worksheets in Excel

In Sheet2, only three columns from the first sheet have been extracted. We’ll show different methods to pull out the price list from the first sheet. We 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

Steps:

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

Go to Sheet2.
Select the first output cell in the Price column.
Right-click and choose the Paste Link option (the clipboard with a link icon).

Auto Populate by Linking Worksheets in Excel

The Price column is now complete with the extracted data from the first sheet (Sheet1).
In Sheet1, change the price of any smartphone model.
Press Enter and go to Sheet2.

Auto Populate by Linking Worksheets in Excel

You’ll find the updated price of the corresponding smartphone in Sheet2.

Auto Populate by Linking Worksheets in Excel

You must keep the order of the devices in both sheets the same as this function blindly copies the range without looking at whether other corresponding values match.

Method 2 – Updating Data Automatically by Using the Equal Sign to Refer to Cells from Another Worksheet

Steps:

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

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

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

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

You must keep the order of the devices in both sheets the same as this function blindly copies the range without looking at whether other corresponding values match.

Read More: How to Autofill a Column in Excel

Method 3 – Using an INDEX-MATCH Formula to Auto Populate from Another Worksheet in Excel

Steps:

Select Cell D5 in Sheet2 and insert 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

Use the Fill Handle to autofill the rest of the cells in Column D.

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

Download the Practice Workbook

Auto Populate from Another Worksheet.xlsx

<< Go Back to Excel Autofill | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Sub MoveData() Dim wsSource As Worksheet Dim wsTarget As Worksheet Dim lastRow As Long Set wsSource = ThisWorkbook.Sheets("Source") 'Change to your source sheet name Set wsTarget = ThisWorkbook.Sheets("Target") 'Change to your target sheet name lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row wsSource.Rows("1:" & lastRow).Copy wsTarget.Cells(wsTarget.Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues wsSource.Rows("1:" & lastRow).ClearContents End Sub

22 Comments

Paul R Hartley Feb 18, 2022 at 11:55 PM

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!
- Reply
  Tanjim Reza Aug 14, 2022 at 2:52 PM
  
  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
ANTHONY Apr 23, 2022 at 6:18 AM

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
- Reply
  Masum Mahdy Aug 14, 2022 at 9:59 AM
  
  We appreciate your nice suggestion, ANTHONY! You can also see the following article to know about all the Paste Options in Excel.
  https://www.exceldemy.com/paste-options-in-excel/
  
  Thank you for being with us. 🙂
Mrs B Feb 13, 2023 at 11:41 PM

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?
- Reply
  Sabrina Ayon Feb 14, 2023 at 2:08 PM
  
  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.
VITO CASA Apr 3, 2024 at 9:39 PM

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 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:
  Using VLOOKUP and IFERROR Functions
  
  Using INDEX, MATCH and IFERROR Functions
  
  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 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:
      
      Choose cell C3.
      
      Insert the following formula: =IFERROR(TEXTJOIN(", ",TRUE, FILTER(Sheet1!$C$3:$C$7, Sheet1!$B$3:$B$7=Sheet2!$B3)), "")
      
      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
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 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:
  IFERROR, TEXTJOIN and IF Functions (New)
  =IFERROR(TEXTJOIN(", ", TRUE, IF(Sheet1!$B$3:$B$11=Sheet2!$B3, Sheet1!$C$3:$C$11, "")), "")
  
  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
Vito May 3, 2024 at 8:26 PM

Good day, Sorry for the late response. I will give this a try and I will report back.
- Reply
  Shamima Sultana May 5, 2024 at 10:44 AM
  
  Hello Vito,
  
  Don’t be sorry Vito. Let us know your feedback, hopefully it will work.
  
  Regards
  ExcelDemy
yulissa alvarez Jun 25, 2024 at 4:36 AM

hi folks, i have a pretty big workbook im working on and am looking for a specific formula or way to auto populate one sheet based on information from another. short version is: in sheet b i need a cell to populate from sheet c based on a name, a contract number, a month, and the task (theres 4 tasks), these are on tables, do i need to convert to range instead?

tldr:
i am tracking invoices for the fiscal year of july 23- june 24. there are 13 different companies we work with, and they invoice us monthly based on 3-4 deliverables depending on contract (there’s two contracts). my invoice sheet has all invoices and the deliverables they are charging us for. my other two sheets have deliverables that were reported online and to another funder, i need to make sure they all match but i dont want to hop between sheets, i want my main sheet to populate those numbers and i can create a conditional format after. keep in mind that the other two sheets are in order of month and company, the invoice sheet is in order of when the invoice was received.
- Reply
  Shamima Sultana Jun 25, 2024 at 4:03 PM
  
  Hello Yulissa Alvarez,
  
  Based on your given scenario created a dummy dataset to auto populate one sheet values based on information from another sheet.
  
  Here I used INDEX-MATCH functions to get data from another sheet dynamically.
  Use the following formulas:
  Task1: =INDEX(SheetC!$D$2:$D$13, MATCH(1, (SheetC!$A$2:$A$13=SheetB!$B2) * (SheetC!$B$2:$B$13=SheetB!$C2) * (SheetC!$C$2:$C$13=SheetB!$D2), 0))
  Task2: =INDEX(SheetC!$E$2:$E$13, MATCH(1, (SheetC!$A$2:$A$13=SheetB!$B2) * (SheetC!$B$2:$B$13=SheetB!$C2) * (SheetC!$C$2:$C$13=SheetB!$D2), 0))
  Task3: =INDEX(SheetC!$F$2:$F$13, MATCH(1, (SheetC!$A$2:$A$13=SheetB!$B2) * (SheetC!$B$2:$B$13=SheetB!$C2) * (SheetC!$C$2:$C$13=SheetB!$D2), 0))
  Task4: =INDEX(SheetC!$G$2:$G$13, MATCH(1, (SheetC!$A$2:$A$13=SheetB!$B2) * (SheetC!$B$2:$B$13=SheetB!$C2) * (SheetC!$C$2:$C$13=SheetB!$D2), 0))
  
  If you want to add more task just change the cell-refernce.
  Output:
  
  You can Download the Excel file:
  Auto Populate Values from Another Sheet
  
  Regards
  ExcelDemy
Brea Kelley Sep 4, 2024 at 4:54 AM

Hi, Love your site! I hope you can help me come up with a solution. I am trying to auto populate venue prices from one sheet to another sheet but have the appropriate price auto populate based on the data entered in column to the left.

Here’s an example: I am working on sheet titled Events. Column F has the names of the venue for each event. Column G would be where the price goes. All prices for each venue is listed in the sheet title Venue Costs. How can I get column G in the Events sheet to populate the appropriate prices from the Venue Costs sheet but pull the correct price based on the venue listed in Column F?

Is that even possible?
- Reply
  Shamima Sultana Sep 4, 2024 at 8:54 PM
  
  Hello Brea Kelley,
  
  Yes, you can auto populate venue prices in Column G of the Events sheet based on the venue listed in Column F by using the VLOOKUP function.
  
  Use the following formula:
  =IFERROR(VLOOKUP(F2, ‘Venue Costs’!$A$1:$B$6, 2, FALSE), “Price not found”)
  
  Change the cell reference of of Venue Costs sheet based on your data.
  
  Download the Excel file:
  Auto Populate Value from Another Sheet.xlsx
  Regards
  ExcelDemy
  - Reply
    TJ Nov 11, 2024 at 8:27 PM
    
    I have a similar situation, however, mine is about parts lists. Each day, our department head sends us a list of orders to fill. We have a master document that contains a breakdown of each item and the parts used to make the products. Our associates manually search this master document daily to use our inventory platform to know how many smaller parts need to be ordered to fulfill the daily orders. I feel like this formula will also work for my specific situation, however, our master document list contains multiple rows of items for the breakdown of parts per item. Is there a way to input the daily list onto a workbook and have Excel input new rows under each item with the detailed breakdown of the parts needed?
    - Shamima Sultana Nov 12, 2024 at 9:57 AM
      
      Hello TJ,
      
      Yes, there is a way to automate your parts breakdown in Excel by using a combination of formulas.
      
      If your master document has a detailed breakdown of parts for each item, and you want to pull this breakdown dynamically into your daily order list, here’s a simple approach using the FILTER function (available in Excel 365 and Excel 2019) or the VLOOKUP function combined with helper columns.
      
      If your breakdown list is well-structured, the FILTER function can pull in all matching parts for a given item. In a cell where you want the parts listed, you can use:
      
      =FILTER(Master!B2:D100, Master!A2:A100 = OrderList!A2, “No parts found”)
      
      Replace Master!B2:D100 with the range containing your parts details, and Master!A2:A100 with the range containing item names in your master list. OrderList!A2 would be the item name in your daily order sheet.
      
      Let me know if you’d like more detailed steps on any of these methods!
      
      Best Regards,
      ExcelDemy
Josi Dec 5, 2024 at 2:48 AM

Hello everyone. I’m hoping to auto move data from one sheet to the other, then have the previous data auto delete without deleting on the new sheet. Is this possible?
- Reply
  Shamima Sultana Dec 5, 2024 at 10:41 AM
  Hello Josi,
  
  Yes, it is possible to move data from one sheet to another automatically while retaining the original data in the new sheet. This can be done using VBA (Visual Basic for Applications) to automate the process. The script can copy data to the target sheet and clear the original while keeping the target data intact.
  
  Here’s a VBA script to automate this process:
  
  1. Press Alt + F11 to open the VBA editor.
  2. Insert a Module and paste the following code:
```
Sub MoveData()
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim lastRow As Long
    
    Set wsSource = ThisWorkbook.Sheets("Source") 'Change to your source sheet name
    Set wsTarget = ThisWorkbook.Sheets("Target") 'Change to your target sheet name
    
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    wsSource.Rows("1:" & lastRow).Copy
    wsTarget.Cells(wsTarget.Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    wsSource.Rows("1:" & lastRow).ClearContents
End Sub
```
  3. Run the Macro to transfer and clear the source data.
  
  Regards
  ExcelDemy

How to Auto Populate from Another Worksheet in Excel

Method 1 – Linking Excel Worksheets to Auto Populate from Another Worksheet

Method 2 – Updating Data Automatically by Using the Equal Sign to Refer to Cells from Another Worksheet

Method 3 – Using an INDEX-MATCH Formula to Auto Populate from Another Worksheet in Excel

Related Articles

Leave a reply Cancel reply