This is a little complex, so unfortunately has gone past my excel skills. As a bit of background info, I'm working on a quick spreadsheet to calculate the cost to ship a parcel to different countries. I've got a worksheet with the list of countries, which services are offered to that country, and what zone for pricing each service runs in.
Using data validation I've got a dropdown menu to pick the destination country, and used vlookup to return the price zones for each service, returning a 0 if that particular service is not available.
Now, I've made a separate worksheet containing our pricing data for each service, so a sheet containing all the express prices, a sheet containing all the standard prices, and so on. This has all been done by hand before with a book, look in the book for the country you want to send to, see what service and what zone. For instance, if I wanted to send something to Germany, that would be zone 3 on the express service, and zone 4 on a standard service. I would then go to the express table, look at the zone 3 column, go down to the weight of the parcel (rounded up) and that would tell me how much it will cost.
To make it a little easier, here's a screenshot with all the values removed.
http://i.imgur.com/mTsp5.jpg
On my front worksheet I have the weight of the parcel, and what zone it is to sent it on the express service. So I somehow need a way to make excel look at the weight, and compare it to the weights in this table, rounding up to the nearest value on the table (Since the increments increase as you get heavies, it starts off increasing by 0.5Kg for the first 10Kg, then increases to 1Kg, and then 2, and so on), and then return the cost from the column of the relevant zone.
For example, if I had a parcel that weighted 20.3Kg going to zone 3, I would want it to look down the weight column, and because 20.3Kg is higher than 20, it needs to use the 22Kg row, and then it goes across to the zone 3 column and returns the value in that cell.
And then I would then copy this formula for each cell, so on my front page it would return the cost from the express sheet using the above, then on the standard sheet it would do the same, just looking at a different sheet and a different zone.
http://i.imgur.com/xgyt5.jpg
There's a copy of the front page. As you can see, I've used Vlookup to return the relevant zone for each service. I now need to use the 'greatest weight' column along with the service level (express plus through to expedited) and use the table I posted before to look down and get the price for that weight going on that service to that zone.
I hope this has all made sense. Its making sense in my head, but that doesn't really mean much, does it?
Anyway, is there an easy way to do this? I assume that the table columns would need to be changed to remove the 'zone' from each column, which is hardly an issue, it should be possible to use the same formula, and just change the worksheet it gets the data from.
Thanks in advance!
Bookmarks