Forum Discussion
Data?
I am trying to take data from one page to another page. I am trying to take a part number on one sheet and have that to another along with its prices so that I can use an excel sheet type part number and the price will co
Let's say you have a sheet PriceList with part numbers in A2:A50 and the corresponding prices in B2:B50.
On another sheet, you enter a part number in D2 and down (or select it from a drop-down list).
Enter the following formula in E2, then fill down.
If you have Microsoft 365 or Office 2021:
=XLOOKUP(D2, PriceList!$A$2:$A$50, PriceList!$B$2:$B$50, "")
If you have an older version:
=IFERROR(VLOOKUP(D2, PriceList!$A$2:$B$50, 2, FALSE), "")
- Thanks for understanding what I am trying to do. I am using Microsoft 365. I tried to select the “price list” columns to add to the formula was successfull there, but do I need the $ in all those spots? Also at the end of the formula there is “” what is that function?
The $ signs make the reference to the ranges on the PriceList sheet absolute, i.e. they won't change when you fill or copy the cell with the formula downwards.
See Switch between relative, absolute, and mixed references
The "" at the end makes the formula return a blank (empty string) if you enter a non-existent part code.
To achieve your goal of transferring data, including part numbers and their prices, from one sheet to another in Excel for the web, you can use various techniques.
Here is a step-by-step approach using formulas:
- Source Sheet: Let us assume your source data is in Sheet1. Column A contains part numbers, and Column B contains their respective prices.
- Target Sheet: Create a new sheet; let us call it Sheet2, where you want to display the part numbers and prices.
- Copying Data: In the first column of Sheet2 (where you want to display the part numbers), you can simply reference the cells from Sheet1 using a formula. In the second column (where you want to display the prices), you will use a similar formula.
- In Sheet2, cell A2 (for the part number):
=Sheet1!A2
- In Sheet2, cell B2 (for the price):
=Sheet1!B2
- Copy these formulas down for as many rows as needed.
- Now, as you enter part numbers in Column A of Sheet1, the corresponding prices from Column B will automatically appear in Column B of Sheet2.
- Data Validation (Optional): To make data entry easier, you can use data validation to create a dropdown list of part numbers on Sheet1. This will help ensure that you select a valid part number and reduce errors.
- Select the cells in Sheet1 where you will be entering part numbers.
- Go to the "Data" tab > "Data Tools" group > "Data Validation."
- In the "Allow" dropdown, select "List."
- In the "Source" field, specify the range of part numbers in Sheet1 (e.g., Sheet1!A2:A100).
- Click "OK."
Now, when you start typing a part number in Sheet1, a dropdown will appear with valid part numbers from the source sheet. Once you select a part number, the corresponding price will automatically appear next to it, referencing the formulas you set up in Sheet2. Remember to adjust cell references and ranges based on your actual data layout.
Please note that the approach in Excel for the web might not provide the same level of automation and dynamic updates as formulas in the desktop version. If you are looking for more advanced functionality, you might need to explore the capabilities of Excel add-ins using Office Script, if supported by the Excel for the web platform. The text and steps were created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
