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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gary - 02 Nov 2007 23:06 GMT

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!

Signature

Gary
Visit Lucy & Gary and do the jigsaw puzzle at
www.under-1-roof.com/PuzzlePage.html

Reply to this Message

Don Guillett - 03 Nov 2007 00:31 GMT

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")))

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> 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
[quoted text clipped - 5 lines]
> cell for each part updated automatically with the most recent cost each
> time I purchase that part. Can anybody help me with this? TIA!

Reply to this Message

Ken Johnson - 03 Nov 2007 01:04 GMT

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

Reply to this Message