 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / February 2008Linking 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 > In a worksheet, I have 2 tables. 1 gives the price for freighting > different sorts of material. The other lists where the material is [quoted text clipped - 28 lines] > > 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 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.  SignatureArvi Laanemets ( My real mail address: arvi.laanemets<at>tarkon.ee ) > In a worksheet, I have 2 tables. 1 gives the price for freighting > different sorts of material. The other lists where the material is [quoted text clipped - 28 lines] > > Reg 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--- SignatureRyGuy > Hi > [quoted text clipped - 57 lines] > > > > Reg 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 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 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 to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.