adding a listbox (E2000)

Topic

New Reply

Good afternoon

Hans, kindly as usual, gave me an extreme amount of help with the calculations worksheet on the attached file (due to size limitations I have reduced the zones to 4 and rates + surcharge to 1 zones but in reality there are about 300 zones and 10 rates and surcharge columns). MY question is in 2 parts

1. Is it possible to make A2 on the calculation worksheet a list box to avoid peoples spelling errors etc.
2. If 1 is possible can you start typing in the box and then go to that letter, for example by default Albania would be the first in the list, so if somebody wanted to select say Venezuala they would have to scroll down the whole list.

As I inherited these worksheet and as an aside to the original question can anybody tell me how B1 in rates and surcharges works please.

Tia

Stephen

Reply | Quote

Replies

Add a combobox from the controls toolbox.
Place it over Cell A2
Right click and select properties:
LinkedCell:
A2
ListFillRange:
Zones!$A$2:$A$5
[Note change range as desired]

You can select from the combobox or type into it.
Steve

Reply | Quote

Hi Steve

Thanks for this, I had in fact already put a combo there when I was trying to do this myself and it does indeed act as a combo, however it does not then perform the calculation based on that Country. If you were to type in Anguilla in A2 as the workbook currently stands it will change the zone to 4 and when the weight is entered it will cross refer to the rates and surcharges sheet to select and calculate the appropriate rate. Hopefully that makes some sense.

Thanks

Stephen

Reply | Quote

Did you fill in A2 as the linked cell as Steve specified?

Reply | Quote

Hi Legare

Thanks for the response, I cannot get internet access at the weekends hence the late response. Yes I made sure that it was linked to A2 as Steve had suggested I should but it was not referring to the formula if that makes sense.

Cheers

Steve

Reply | Quote

Hi Legare

Thanks for the response, I cannot get internet access at the weekends hence the late response. Yes I made sure that it was linked to A2 as Steve had suggested I should but it was not referring to the formula if that makes sense.

Cheers

Steve

Reply | Quote

Did you fill in A2 as the linked cell as Steve specified?

Reply | Quote

One problem is the range you index:
‘Rates + Surcharge’!$B$4:$C$144

Only has 2 columns and if zone is 4 there is no 4th column to lookup. This has nothing to do with the Combobox, it has to do with the setup of “Rates +Surcharge”

I assumed it was just “odd” because you deleted some columns” to reduce the size.
Steve

Reply | Quote

Hi Steve

I used your answer on the full blown version of my workbook so the calculation (as far as I understand it) should have worked, I will perservere though

Cheers

Steve

Reply | Quote

Hi Steve

I used your answer on the full blown version of my workbook so the calculation (as far as I understand it) should have worked, I will perservere though

Cheers

Steve

Reply | Quote

Are you using the combobox from controls toolbox?

If you use the combobox from forms toolbar, A1 will get a number not the “text”. If you use the forms toolbar, instead of Vlookup you can use INDEX directly and not “lookup” the value, since you would already have the “row” in A2 not the text to lookup.

The easiest way to check is to see what the value in A2 is. Is it a number or is it text. Is it the number/text that you expect?

Steve

Reply | Quote

Hi Steve

Thanks for your advice so far which I am trying to understand and process, my knowledge of Excel is limited (extremely) and a lot of what you have advised is quite frankly beyond me. I inherited this workbook with just the first 2 pages and I posted a requested to the effect that could it be made interactive to calculate the rates based on a destination country and then to calculate the rates based on weight and zone, Hans as mentioned kindly supplied page 3 for me which I have been trying to breakdown and understand with limited success.

A2 on the existing calculation page takes its values from the zones page although I cannot see how this happens as no formula shows when I am inside cell A2 (I have tried the help feature for show formulas but it does not show anything), when you type in a country name and press tab the zone in B2 automatically enters its self using the formula =VLOOKUP(A2,Zones!A4:B215,2,FALSE), when the weight is input into C2 and the weight is calculated and shown in I2 (obviously there are other hidden columns D,E,F,G 2 that behave in a similar way to I2) using the formula =IF(D2<=70.5,E2,IF(D2<=99,F2,IF(D2<=299,G2,IF(D2<=499,H2,"Unknown")))).

Putting in the Combo as suggested certainly allows me to select the Country from the Zones page but it does not allow me to tab out of it the update B2 and then enter the weight in C2 to do the calculation.

I hope this makes sense and is not asking too much of you.

TIA

Stephen

Reply | Quote

You can use Validation to show a dropdown list in A2, but it won’t autocomplete a name typed by the user. You can use Tab, however, to get out of the dropdown list.
– Select the list of countries on the Zones sheet (A2:A5 in the stripped down workbook you posted)
– Select Insert | Name | Define…
– Type Countries in the Names box, then click OK.
– Select A2 in the Calculation worksheet.
– Select Data | Validation…
– Select List in the Allow dropdown list.
– Enter =Countries in the Source box.
– Switch to the Error Alert tab of the Validation dialog.
– Make sure that Stop is selected.
– Enter an error message.
– You can also specify a help message in the middle tab of the dialog.
– Click OK.

Reply | Quote

Thanks Hans

Fantastic advice as usual, sorry for late response but since being laid of it is difficult for me to access the internet. Also many thanks Steve for your assistance

Cheers

Stephen

Reply | Quote

I’m sorry to hear you lost your job. I hope that you’ll find something new soon.

Reply | Quote

You can use Validation to show a dropdown list in A2, but it won’t autocomplete a name typed by the user. You can use Tab, however, to get out of the dropdown list.
– Select the list of countries on the Zones sheet (A2:A5 in the stripped down workbook you posted)
– Select Insert | Name | Define…
– Type Countries in the Names box, then click OK.
– Select A2 in the Calculation worksheet.
– Select Data | Validation…
– Select List in the Allow dropdown list.
– Enter =Countries in the Source box.
– Switch to the Error Alert tab of the Validation dialog.
– Make sure that Stop is selected.
– Enter an error message.
– You can also specify a help message in the middle tab of the dialog.
– Click OK.

Reply | Quote

I am not sure I understand your question.

If you select a name from the combobox, it will add a value to A2. B2 will determine (via the lookup formula) the zone based on the value in A2. All you have to do is select the cell C2 and enter in the number.

You will still have to fix the problems with the problems in the range you index (mentioned in post 440920).

If you want to tab out you can use datavalidation, though it won’t autocomplete as you type, and it is a lot less “secure”. Post back if you need help with validation. With a long list of names the combo is better, even if they have to select C2 with the mouse. It still beats scrolling thru a long list with a mouse.

Steve

Reply | Quote

I am not sure I understand your question.

If you select a name from the combobox, it will add a value to A2. B2 will determine (via the lookup formula) the zone based on the value in A2. All you have to do is select the cell C2 and enter in the number.

You will still have to fix the problems with the problems in the range you index (mentioned in post 440920).

If you want to tab out you can use datavalidation, though it won’t autocomplete as you type, and it is a lot less “secure”. Post back if you need help with validation. With a long list of names the combo is better, even if they have to select C2 with the mouse. It still beats scrolling thru a long list with a mouse.

Steve

Reply | Quote

Hi Steve

Thanks for your advice so far which I am trying to understand and process, my knowledge of Excel is limited (extremely) and a lot of what you have advised is quite frankly beyond me. I inherited this workbook with just the first 2 pages and I posted a requested to the effect that could it be made interactive to calculate the rates based on a destination country and then to calculate the rates based on weight and zone, Hans as mentioned kindly supplied page 3 for me which I have been trying to breakdown and understand with limited success.

A2 on the existing calculation page takes its values from the zones page although I cannot see how this happens as no formula shows when I am inside cell A2 (I have tried the help feature for show formulas but it does not show anything), when you type in a country name and press tab the zone in B2 automatically enters its self using the formula =VLOOKUP(A2,Zones!A4:B215,2,FALSE), when the weight is input into C2 and the weight is calculated and shown in I2 (obviously there are other hidden columns D,E,F,G 2 that behave in a similar way to I2) using the formula =IF(D2<=70.5,E2,IF(D2<=99,F2,IF(D2<=299,G2,IF(D2<=499,H2,"Unknown")))).

Putting in the Combo as suggested certainly allows me to select the Country from the Zones page but it does not allow me to tab out of it the update B2 and then enter the weight in C2 to do the calculation.

I hope this makes sense and is not asking too much of you.

TIA

Stephen

Reply | Quote

Are you using the combobox from controls toolbox?

If you use the combobox from forms toolbar, A1 will get a number not the “text”. If you use the forms toolbar, instead of Vlookup you can use INDEX directly and not “lookup” the value, since you would already have the “row” in A2 not the text to lookup.

The easiest way to check is to see what the value in A2 is. Is it a number or is it text. Is it the number/text that you expect?

Steve

Reply | Quote

One problem is the range you index:
‘Rates + Surcharge’!$B$4:$C$144

Only has 2 columns and if zone is 4 there is no 4th column to lookup. This has nothing to do with the Combobox, it has to do with the setup of “Rates +Surcharge”

I assumed it was just “odd” because you deleted some columns” to reduce the size.
Steve

Reply | Quote

Hi Steve

Thanks for this, I had in fact already put a combo there when I was trying to do this myself and it does indeed act as a combo, however it does not then perform the calculation based on that Country. If you were to type in Anguilla in A2 as the workbook currently stands it will change the zone to 4 and when the weight is entered it will cross refer to the rates and surcharges sheet to select and calculate the appropriate rate. Hopefully that makes some sense.

Thanks

Stephen

Reply | Quote

Add a combobox from the controls toolbox.
Place it over Cell A2
Right click and select properties:
LinkedCell:
A2
ListFillRange:
Zones!$A$2:$A$5
[Note change range as desired]

You can select from the combobox or type into it.
Steve

Reply | Quote

Concerning B1:
C655372 to C65512 is the “base values” for rows C4:C144 and it just adds the percentage increase from B1

The value in B4 is (for example)
=SUM(‘Rates + Surcharge’!C65372*((100+$B$1)/100))

It adds a “base” from C655372 of that sheet and adds the percentage from B1 to it

Steve

Reply | Quote

Concerning B1:
C655372 to C65512 is the “base values” for rows C4:C144 and it just adds the percentage increase from B1

The value in B4 is (for example)
=SUM(‘Rates + Surcharge’!C65372*((100+$B$1)/100))

It adds a “base” from C655372 of that sheet and adds the percentage from B1 to it

Steve

Reply | Quote