Results 1 to 7 of 7
  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

    listbox problem (e2000)

    Hi all

    I have a workbook with 2 sheets, sheet 1 = master and sheet 2 = secure

    on sheet 2 I have 2 listboxes that calculate a transport rate. Listbox 1 takes its input range from the master sheet as follows master:A6:A405 which then populates the listbox with town names. The second listbox contains details of vehicle sizes so that when the user selects a town from listbox 1 and a vehicle size from listbox 2 a price is returned into the appropraite cell.

    Sometimes a customer will request a 2 man crew, for the second man we calculate that a van will travel at an average speed of 50 miles per hour and that he will be paid

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

    Re: listbox problem (e2000)

    The formula

    =VLOOKUP(H1,Master!A6:B405,2,FALSE)

    looks for the value of H1 in the first column of Master!A6:B405 and returns the value from the second column in the row where the value was found.

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

    Re: listbox problem (e2000)

    Thank you Hans

    I have entered this information into a cell and it returns a N/A retsult , any ideas please, also can you please let me know what the ,2, part refers to in your response.

    Thanks


    Stephen

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

    Re: listbox problem (e2000)

    Sorry Hans I have given you incorrect information to work on.

    What I did not realise is that cell H1 in worksheet 2 shows the row number (from the master sheet) of the destination selected in the listbox, but I cannot see how it is getting its values as there is no formula showing. The list box as mentioned shows its source as Master!A6:A405 and is linked to H1, any other ideas please?

    Stephen

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: listbox problem (e2000)

    I am not sure I understand your setup exactly, but does :

    <pre>=Index(Master!B6:B405,H1)</pre>


    gIve you what you want?

    Steve

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

    Re: listbox problem (e2000)

    Thank you Steve

    That worked great and returns the mileage for the city selected, in my original question I eluded to the fact that what I was trying to do was to calculate the cost of a 2nd man based on mileage. The result I now get if I slect for example York is 422 which is the mileage from my office to York UK and I can now cost for the 2nd man by formatting a cell to read =G22/50*10 to arrive at the cost, however the minimum charge for a second man is

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: listbox problem (e2000)

    <pre>=Max(G22/50*10 ,20)</pre>


    Steve

Posting Permissions

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