Hi Jorge, Looks like this can be handled using lookup formulas.
One good way to perform lookups is to have a number representing each of your criteria each alone in their own cell, preferably all in the same table for all transporters. You can use pivot tables to generate the cross referenced charts you have now for easy human-reading for each shipper, and your Cost table can use lookup formulas to do its calculation. I'm a bit confused about a few details, and don't have a lot of time right now, but will try to take another look in the coming days if you haven't a solution yet. Some questions though: You refer to 6 items of data: (1) Transporter (2) Zone (3) Volumes (4) Weight/KG (5) Distance/KM (6) Cost. The charts for each Transporter seem to contain 4 items of data. 5 including the name of the transporter. I assume the row 2 of the transporter tables contain ranges for VOLUMES, row 3 ranges for WEIGHT/KG; Column 1 zontains ZONE. The data points in cells B4:D7 then must contain either DISTANCE/KM or COST… But which? And where is the other item? It's possible to perform a lookup that checks multiple criteria and finds the intersection on a table like this. Perhaps--since the ranges for volumes and KG seem to be the same for all transporters, as do the number of zones--insert another column either before or after the zone column for the Shipper name/ID, and include all transporters' data in the one table while maintaining a similar format. You can then use a lookup strategy that might go like this: use MATCH functions for each criteria, and logic to determine the right combination of criteria, then use the index function to retrieve the selected column, and another index function to retrieve the selected row. Place a space between those two functions (all in the same formula) and Excel will return the intersection of that row and column, which will be a single matching cell. Asa From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Jorge Marques Sent: Friday, June 29, 2012 1:54 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ What do you recommend?? If Formulas, Tables with macros, vlookups? Hi guys i have a sheet where I every month i calculate the costs for logistics, there are 3 conditions (KM, Weight in KG, and Volumes), where i have to calculate depending on the data put in the columns B,C,D if it is zone 1,2,3 and has the volumes or weight under those limits it puts those values. if zone 4 and under those 3 limits we have to multiply the value in the table with the extra km in column D. Until now i only had 2 transport company´s so I used if formulas to calculate the costs, but now I have 3 more, a total of 5, and I think if i use if formulas it will be heavy on the code and generate errors. What do you recommend? Thank you very much in advance. -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ---------------------------------------------------------------------------- -------------------------- To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
