# Thread: Combo Boxes & Formula's (2003)

1. ## Combo Boxes & Formula's (2003)

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

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

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

Buddy,
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.