Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Dec 2003
    Location
    London, England
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo Boxes & Formula's (2003)

    Please see the attached
    The problem I am having is on the international part of the the Input Sheet.
    The complete A-Z of countries has been shortened to enable this sheet to be sent.
    The International section is different to the UK Local / National and UK Mobile in that those two sections the destinations are pre-set.
    On the International side any country from the International Country List could be chosen.
    The "Their 1" combo box determines what is seen in the combo box below - All the tariff details can be seen on the "Tariff Detail" sheet.
    In cell K40 how can I pick up the correct tariff price ??

  2. #2
    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: Combo Boxes & Formula's (2003)

    I am not sure what number from the Tarif list you want in K40. Could you elaborate?

    Steve

  3. #3
    Lounger
    Join Date
    Dec 2003
    Location
    London, England
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Boxes & Formula's (2003)

    When you type in the International desination (on the example "Input sheet" cells B40 to B45) the

  4. #4
    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: Combo Boxes & Formula's (2003)

    Does this work for you?

    Steve

    Create a name for the Lookup table:
    Insert - name - define
    Name: LookupList
    Refers to:
    =OFFSET('BT Tariff Detail'!$A$1,3,CHOOSE('Input Sheet'!$K$3,1,8,13,17),100,6)
    [Note change the 100 to the number of rows in in the lookup tables]
    This will define the table based on the Their1- Their4
    [Note:If you had the tables spaced evenly (always starting at a multiple- leaving blanks if there are not as many columns) you could eliminate the choose and just calculate from the K3 value]

    Then in K40 add this formula:
    =IF(B40="","",VLOOKUP(B40,LookupList,$K$5+1,0))
    Copy K40 to K41:K45

    This lookups in the table defined by their1-their4 based on the country ("row") and the column chosen in second combobox.

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Boxes & Formula's (2003)

    Buddy,
    After a bit of study of your worksheets I made a few adjustments:
    To start with I find it much easier to work with my sheets and the formulas if I give names to the ranges.
    Thus I added the following range names
    Carrier
    Each Carrier
    Plan
    Level
    LookupTable
    TariffTable1 thru 4

    I added a couple of drop-downs which parallel yours.

    I then changed the formulas in K40 -K45
    This is just a start to demonstrate the process. The remaining cells with #REF errors would need adjusting as well.
    To me this makes it many times easier to follow what is happening (or not happening) in my formulas and make corrections etc.
    In addition items can be added to or deleted from a range with much less adjusting.

    Another idea: in the formulas in K40-45 I used the VLOOKUP function (as you do as well in other cells). This works as long as your lookup list ( countries in this case) is in alphabetical order and has no blanks. An alternative which gets around this limitation is to use INDEX-MATCH rather than VLOOKUP

    Just some food for thought. HTH

Posting Permissions

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