Hi
In 2nd sheet, add 4th column Price
D2=C2*VLOOKUP($B2,Prices,2,0)
, and copy down.
(I assume, materials and prices on price sheet are in different cloumns, and
prices are really numbers formatted as currency, not strings, and of course
that materials are there without any colons - otherwise the formula will be
a way more complicated)
Prices in formula is the range in price sheet, with includes whole price
table except header. Yuo replace it with real absolute range reference, or
you define according named dynamic range before.
You create a 3rd sheet Sites, where all sites are listed in column A, like
Site
Site1
Site2
Site3
Into B1, enter header for 2nd column Price
B2=SUMPRODUCT(--(MaterialSite=$A2),MaterialAmount,MaterialPrice)
, and copy down.
MaterialSite refers to all data in column A (without header) of material
usage site, MaterialAmount refers to range with amounts on same sheet, and
MaterialPrice to prices in colun D on same sheet. Again, you can define
according dynamic ranges.
PS. All ranges used in sumproduct MUST be of same dimension, i.e. include
same number of rows.
--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )
"Regnab" wrote in message
...
In a worksheet, I have 2 tables. 1 gives the price for freighting
different sorts of material. The other lists where the material is
needed. EG
DirtA: $100
DirtB: $250
DirtC: $30
DirtD: $80
Site1 DirtB 100kg
Site1 DirtC 270kg
Site2 DirtA 300kg
Site2 DirtB 150kg
Site3 DirtB 120kg
What I need to do is have a summary for each site on how much the
freight will cost for each site. It 'can' be done using sumproduct and
adding each dirt type together, but the problem is that there are 20
different dirt types for each site so the formula is too long.
What I am asking is is there a way to 'combine' the tables in a
formula, where it would multiply the kgs of each dirt type by the
freight cost to provide the overall freight cost for each site. IE
Site 1: (250 x 100kg)+(30 x 270). I could do it easily in Access by
linking the primary key but I'm not quite as up to speed with excel.
Any assistance or tips on where to look or what to search for would be
much appreciated.
Regards
Reg