Linking two "tables" of data.

[Regnab]

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

 

[Matt Richardson]

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

The first thing I'd recommend you do is use a vlookup between the
tables so that you can get your prices into the second table. You can
find more information about VLOOKUPs here:-

http://teachr.blogspot.com/2006/07/vlookup-tutorial-excel-intermediate.html

Following that a pivot table might be useful to get summary details.
Microsoft have a good link on using Pivot Tables here:-

http://www.microsoft.com/dynamics/using/excel_pivot_tables_collins.mspx

Hope this helps,
Matt Richardson
http://teachr.blogspot.com

 

[Arvi Laanemets]

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.

 

[ryguy7272]

Take a look at these links:

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=553

http://peltiertech.com/Excel/Pivots/pivottables.htm

http://peltiertech.com/Excel/Pivots/pivotcharts.htm

http://www.contextures.com/xlfaqPivot.html


Post back if you have any additional questions


Regards,
Ryan---

 

[Regnab]

Thanks for all the suggestions - I'm still looking through them.

The one thing I realised that I didn't explain is that the kilo's of
freight and freight charges are for each month of the year, over a
number of years. In effect, I'm looking for a monthly freight total.
The VLOOKUP suggestion would definately work well for a month on it's
own, but when it's over a number of columns, it would get a little
messy (unless I misunderstood). I'd be happy to send/post an example
if that would make it easier to explain. I'll try and do a simplified
example below:

Price per kg of dirt
DIRT JAN FEB MAR APR etc
DirtA: $100 110 105 102
DirtB: $250 244 240 233
DirtC: $30 41 50 23
DirtD: $80 65 76 89


Kgs of dirt required

SITE DIRT JAN FEB MAR APR etc
Site1 DirtA 100 250 140
Site1 DirtC 230 340 340
Site1 DirtD 900 200
Site2 DirtA 340 234 230

TOTAL
FREIGHT 50900 etc etc etc

Like I said, there might be 20 dirt types on 5 sites. The freight in
one month for one type of dirt is consistent for all sites. And yes
Arvi, the figures are stored as numbers, not strings. FYI, these
tables are on the same sheet (not that is makes a real lot of
difference).

Thanks again, and I'll check out if pivot tables would do the job.
i've used them in Access a lot, but not so much in excel...

Regards

Reg

 

[Regnab]

Thanks for all the suggestions - I'm still looking through them.

The one thing I realised that I didn't explain is that the kilo's of
freight and freight charges are for each month of the year, over a
number of years. In effect, I'm looking for a monthly freight total.
The VLOOKUP suggestion would definately work well for a month on it's
own, but when it's over a number of columns, it would get a little
messy (unless I misunderstood). I'd be happy to send/post an example
if that would make it easier to explain. I'll try and do a simplified
example below:

Price per kg of dirt
DIRT JAN FEB MAR APR etc
DirtA: $100 110 105 102
DirtB: $250 244 240 233
DirtC: $30 41 50 23
DirtD: $80 65 76 89


Kgs of dirt required

SITE DIRT JAN FEB MAR APR etc
Site1 DirtA 100 250 140
Site1 DirtC 230 340 340
Site1 DirtD 900 200
Site2 DirtA 340 234 230

TOTAL
FREIGHT 50900 etc etc etc

Like I said, there might be 20 dirt types on 5 sites. The freight in
one month for one type of dirt is consistent for all sites. And yes
Arvi, the figures are stored as numbers, not strings. FYI, these
tables are on the same sheet (not that is makes a real lot of
difference).

Thanks again, and I'll check out if pivot tables would do the job.
i've used them in Access a lot, but not so much in excel...

Regards

Reg

 

[Regnab]

Thanks for all the suggestions - I'm still looking through them.

The one thing I realised that I didn't explain is that the kilo's of
freight and freight charges are for each month of the year, over a
number of years. In effect, I'm looking for a monthly freight total.
The VLOOKUP suggestion would definately work well for a month on it's
own, but when it's over a number of columns, it would get a little
messy (unless I misunderstood). I'd be happy to send/post an example
if that would make it easier to explain. I'll try and do a simplified
example below:

Price per kg of dirt
DIRT JAN FEB MAR APR etc
DirtA: $100 110 105 102
DirtB: $250 244 240 233
DirtC: $30 41 50 23
DirtD: $80 65 76 89


Kgs of dirt required

SITE DIRT JAN FEB MAR APR etc
Site1 DirtA 100 250 140
Site1 DirtC 230 340 340
Site1 DirtD 900 200
Site2 DirtA 340 234 230

TOTAL
FREIGHT 50900 etc etc etc

Like I said, there might be 20 dirt types on 5 sites. The freight in
one month for one type of dirt is consistent for all sites. And yes
Arvi, the figures are stored as numbers, not strings. FYI, these
tables are on the same sheet (not that is makes a real lot of
difference).

Thanks again, and I'll check out if pivot tables would do the job.
i've used them in Access a lot, but not so much in excel...

Regards

Reg