Matching value
Posted by David Stanley on January 04, 2002 11:28 AM
I have two worksheets. One is a master sheet with a column of all our inventory item numbers and beginning inventory qty and values. My other work sheet has a column with inventory item numbers that I purchased this month, qty and value. I want to update my master sheet, beginning inventory, with purchases. My columns of item numbers don't match because my purchases worksheet only has item numbers that were purchased. I can do this in Access with linking tables and running update queries, but would like to update in excel. Is it possible?
Thank you for your consideration.
Posted by Scott on January 04, 2002 1:18 PM
I'm assuming that the items numbers on the master are the same as the ones on the buy sheet. You can use a Vlookup formula for this. Lets assume that the item numbers are in Column A on both sheets, and the quantities are in Column B. In column C on the master you can use this formula:
=VLOOKUP(A1,Sheet2!A1:B100,2,0)
If the product is not on the second sheet it will return "#N/A". If you don't want to see this, you can use this formula:
=IF(ISNA(VLOOKUP(A1,Sheet2!A1:B100,2,0)),0,=VLOOKUP(A1,Sheet2!A1:B100,2,0))
You can replace the ,0, with ,"", if you'd rather see blank cells.
Posted by David Stanley on January 05, 2002 6:02 AM
Scott, I really appreciate your timely reply. The quantities on sheet 2 are in the 8th column. The item numbers are both in second columns. I need to update the 10th column on maste sheet, Iassue this is done by placing formula there. I would also like to add filters such a update master quantity if from company ABC and type XYZ.
Posted by David Stanley on January 08, 2002 8:18 AM
Scott, I really appreciate your timely reply. The quantities on sheet 2 are in the 8th column. The item numbers are both in second columns. I need to update the 10th column on maste sheet, Iassue this is done by placing formula there. I would also like to add filters such a update master quantity if from company ABC and type XYZ.
