Method 1 – Linking Excel Worksheets to Auto Populate from Another Worksheet
Sheet1 contains some specifications of smartphone models.
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).
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.
- 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).
- 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.
- You’ll find the updated price of the corresponding smartphone in Sheet2.
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.
- Go to Sheet1.
- Select the range of cells (F5:F13) containing the prices of all smartphone models.
- Press Enter.
- 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.
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 the Fill Handle to autofill the rest of the cells in Column D.
Read More: Excel Formulas to Fill Down Sequence Numbers Skip Hidden Rows
Download the Practice Workbook
Related Articles
- How to AutoFill Formula When Inserting Rows in Excel
- How to Fill Column in Excel with Same Value
- How to Autofill Dates in Excel
- Filling a Certain Number of Rows in Excel Automatically
- How to Fill Down to Last Row with Data in Excel
<< Go Back to Excel Autofill | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
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
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
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. 🙂
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.
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.
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:
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
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
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:
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
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?
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:
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
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
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.
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
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?
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
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?
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