How can I update the latest cost paid for a part?

[Gary]

I'm not very experienced with Excel but I'm trying to use 2003 to keep a
parts inventory. I'm keeping the inventory of parts on one sheet and the
purchase record of parts that I've purchased on another sheet. The purchase
record consists mainly of a column with the purchase date, a column with
the part number and a column with the cost per each for that order. The
inventory sheet contains a column for part number and a column for cost per
each. I'd like to use a formula to keep the "cost per each" cell for each
part updated automatically with the most recent cost each time I purchase
that part. Can anybody help me with this? TIA!

 

[Don Guillett]

These are ARRAY formulas that must be entered using ctrl+shift+enter

=INDEX(C:C,MAX(ROW(2:100)*(A2:A100<>"")*(B2:B100="b")))
if data on sheet 2
=INDEX(Sheet2!C:C,MAX(ROW(2:100)*(Sheet2!A2:A100<>"")*(Sheet2!B2:B100="b")))

 

[Ken Johnson]

If a Sheet named Purchases has Date in column A, Part Number in column
B and Cost in column C
and a Sheet named Parts has Part Number in Column A, then the
following formula in Column B on the Parts Sheet will return the most
recent price for the part in column A from the first 199 rows of
purchases...

=VLOOKUP(SUMPRODUCT(MAX((Purchases!$A$2:$A$200)*(Purchases!$B$2:$B
$200=Parts!A2))),Purchases!$A$2:$C$200,3,FALSE)

Ken Johnson