I have a workbook that I'm using to keep track of product that I've ordered. At the moment, it has 2 sheets - one is an inventory list of each item organized in rows with all the particulars (product ID, min/max quantities, description, price etc) in each column. On the second sheet, I keep track of when items have been ordered along with the purchase order numbers. Currently, I copy the row of the item I'm ordering from the inventory sheet and paste it on the second sheet with the purchase order number, date and quantity ordered. I keep this as a running list that I keep adding to so that I can see trends and order history. Is there a way to, on the second sheet, type in the product ID, hit enter and have it populate the row with the info of the same product ID from the inventory sheet? It would save a huge amount of time to eliminate the copy and paste since there are thousands of product IDs and their info.
Thanks.
11 Answer
For that size of a collection, maybe you should look into using Access, or some other database solution. Still, here goes.
Use the VLOOKUP function in Excel. As an example:
Sheet1 has this layout
| A | B | C | D | E |
1 | Product ID | Max Q | Min Q | Desc. | Price |
2 | ######### | ### | ## | xxxxx | 34.29 |
...
2359 | ######### | ### | ## | xxxxx | 54.28 |Sheet2 has this layout
| A | B | C | D | E | F | G |
1 |Order date | Recv Date | Order Q | P.O. Num. | Product ID | Desc. | Price |
2 | | | | | | | |In Sheet2 cell E2 you enter the Product ID that matches some ID in Sheet1 column A.
In Sheet2 cell F2 you enter the formula =VLOOKUP(E2,Sheet1!$A$2:$E$2359,4,TRUE) and in G2 you enter the formula =VLOOKUP(A1,Sheet1!$A$1:$H$2359,5,TRUE). The range references are absoluted $ so they don't change as the formula is copied down the page, and the 3rd parameter is the column "inside" the range that has the value you want. If, for example, Sheet1 started in column H, then the range would change to Sheet1!$H$2:$L$2 but the 4 for Desc. and the 5 for Price would not change. You can get around having to update the range in the formulas every time you add a product by using a named range, check Excel's help for that. The product ID column needs to be sorted for best effect based on your reported dataset size, though not required by VLOOKUP, and you will have to copy the formulas from row 2 down as far as you think you need them (however many thousand rows that might be.) If the sheets have names then place them inside single quotes, like =VLOOKUP(E2,'Inventory List'!$A$2:$E$2359,4,TRUE). In the formula, the 1st argument E2 is the cell to match against, the 2nd argument Sheet1!$A$1:$H$2359 is the range to look in - the search is only done in the first column, the 3rd argument is the column in the range for the data to return, and the 4th argument TRUE forces an exact match.
Seriously.. consider migrating such a large dataset to a database. It'll mean more work now, but a lot better in the long run, including the ability to spot trends easier with less hassle.
2
