Window Excel

killaspec

I am working with excel and was wondering if there was a way that I could link a sum. Ok not sure if I am explaining this right basically say I have 30 products and each has a product number I am trying to find out what profit I make can I make a macro that will allow me to find just that product number to minus the cost price from my sale value.

I.E product 10001 (Which is in my product file in the spreedsheet) costs me €3.00 when I type in on the may cash book part of my excel sheet that product code it will automatically show my cost price.

Hope this all makes sense.

Killaspec

Basq

Make at table with product details and use VLOOKUP - that should do it.

killaspec

basquille wrote: »

Make at table with product details and use VLOOKUP - that should do it.

Thanks checking it out now.

Thoie

I'm not 100% if I've got the right end of the stick, but I think the formula you want is vlookup

So, say on the first sheet (Called "Product" in my example you have
Product Cost
10001 €3
10002 €5


Then on sheet "May" you have

Job# Part# SalePrice Profit
123 10001 10 =C2-(VLOOKUP(May!B2,Product!A:B,2,FALSE))


What the formula is doing is subtracting 3 (the cost price) from 10 (the sale price).

As long as the product numbers are in numerical order, this will work.

Breaking down the formula,
May!B2 is the part number on the May spreadsheet
Product!A:B is the range you're looking up on the Product sheet - in my case columns A and B (Product and Cost)
2 means that you want the value from the second column (the cost price)
FALSE means that you want only exact matches

That help?

killaspec

Thanks a million works like a dream guys. Trust me this has had me running around all night I am truely thankful for all your help.

Take care

Killaspec