Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculate result from other WS's (E2002)

    Hi

    If the following is to much to ask please ignore this request.

    I have inherited a workbook that I would like to adapt, as my skills are non existant and the more I learn here the less I seem to know, I would like to ask for quite a bit of advice and hints on what to do. There are 2 WS in the WB (copy attached)

    WS1 has 2 columns A = Countries and B = Zones

    WS2 has 11 columns A = Kilos, B = Zone 1, C = Zone 2 through to Zone 10

    Column A lists the weight breaks in half kilo increments from 0.50 up to 70.5 kilos and the 'Zone' columns have the associated rates.
    Above 70.5 kilos the user will be charged per kilo, there are 3 different weight breaks 71-99 kilos, 100 - 299 kilos, 300 - 499 kilos

    What I was wanting to achieve is to have another WS which would have a listbox (or lookp box?) that would look up column A and B in WS1 (I tried to adapt an example that HansV posted 5-384622-Demo), there would then be a cell for the user to enter a weight and then another cell that would return a rate that is calculated from the Country, the zone and the weight. Without the lookup box option I tried the formula using a Vlookup column ranges $B$,$$K$ but I got so tied up in knots it was'nt funny.

    Any help, advice etc. will be warmly welcomed

    Cheers

    Stephen

  2. #2
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculate result from other WS's (E2002)

    Hi Hans,

    Thank you, that must have been the quickest answer I have seen.

    The Country and the zone work fine, however when I put in 55.5 kilos I would expect to see

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculate result from other WS's (E2002)

    I'm sorry, something seems to have gone wrong with the workbook I attached. Here is the hopefully correct version.

    You can look at the formulas in cells B2 and D2:I2 to see how it works.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculate result from other WS's (E2002)

    Does the attached do what you want? Intermediate results are displayed in gray, they can be hidden in the final version.

    Added: attachment removed, it was incorrect. See my reply below.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •