Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Linking two "tables" of data.
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Linking two "tables" of data.
On Feb 13, 6:57 am, Regnab wrote:
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 he- http://teachr.blogspot.com/2006/07/v...ermediate.html Following that a pivot table might be useful to get summary details. Microsoft have a good link on using Pivot Tables he- http://www.microsoft.com/dynamics/us...s_collins.mspx Hope this helps, Matt Richardson http://teachr.blogspot.com |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Linking two "tables" of data.
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Linking two "tables" of data.
Take a look at these links:
http://pubs.logicalexpressions.com/P...cle.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--- -- RyGuy "Arvi Laanemets" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Linking two "tables" of data.
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Linking two "tables" of data.
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Linking two "tables" of data.
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Defining Multiple "named" data ranges for Piot Tables in Excel 200 | Excel Discussion (Misc queries) | |||
problem with Linking workbooks via "copy" and "paste link" | Excel Discussion (Misc queries) | |||
Macro to concatenate into "B1" B2 thru B"x" based on new data in "Col A" | Excel Discussion (Misc queries) | |||
Pivot Tables - How can I "reset" the selections in "Row Field"? | Excel Discussion (Misc queries) | |||
Linking two "total" pages to create a "Complete Total" page | Excel Worksheet Functions |