Conditional Combo Boxes in Excel - HELP NEEDED

[lunatism]

Hi, i'm well into developing my own system (and will be hoping to post selections once completed and backtested) and i'm looking to facilate for every league that bet365 currently accomodate for (i had them email me a list). and what i'm trying to do at the moment is speed up the input process by having teams appear from combo boxes. initially i had just uk, scotland and wales, but now (as mentioned above) i want loads more. to do this i think i need some kind of conditional combo box, where first i can choose a continent, then country (in the next box), then league (in next box), then team (in final box).....soooooo the list in each box depends on what was chosen in the box before it. i tried this with if statements in excel 2007, and it works to an extent...BUT although an improvement on previous excel versions, 64 nested if's can't facilitate for the 150 odds leagues i'm trying to incorporate! Thanks!! BTW: i can provide a link that demonstrates exactly what i mean if necessary

[mcgin]

Re: Conditional Combo Boxes in Excel - HELP NEEDED Have you looked at VLOOKUP? http://msdn2.microsoft.com/en-us/library/aa140121(office.10).aspx

[muppet77]

Re: Conditional Combo Boxes in Excel - HELP NEEDED do you want a drop down list so you can select a team from it? this is easy to do. let me know. do you really need to go through continent, country, league etc, or, if you have every team listed in the column, say from a1:a500, when you start typing in a501 it will try to predict which club you are typing. try it.

[lunatism]

Re: Conditional Combo Boxes in Excel - HELP NEEDED

Have you looked at VLOOKUP? http://msdn2.microsoft.com/en-us/library/aa140121(office.10).aspx

Thanks :notworthy mcgin, after some intials trials with letters and numbers, vlookup seems to do the job. The way i do it (to get past any two combo box limitations) is to produce a unique number from a combination of the (for example) first two combo box values, and then the vlookup table looks for that unique value in the vlookup table (with an extra column containing the known unique values) and the third (fourth and five, etc) combo box is linked to the vlookup formula. simple! or perhaps too complicated? what do you think?

do you want a drop down list so you can select a team from it? this is easy to do. let me know. do you really need to go through continent, country, league etc, or, if you have every team listed in the column, say from a1:a500, when you start typing in a501 it will try to predict which club you are typing. try it.

oh, i know how to make a drop down list with every team in it, but the reason why i wanted continent, club, country, etc is because the list was way too long! I would need to scoll for ages to access a particular team, which could lead to errors. And I'm working from a mini tablet pc so the screen doesn't have the space for huge lists to look through. of course, doing it your way would be much more simple, i wouldn't even need to scroll down, it could just predict from the typing. BUT, if there is a way (and :hope vlookup works) i'd rather not type anything and be able to select either by league or country - my keyboard isn't too easy to type into too - PLUS theres all the weird characters you get in the south american, german, etc teams that aren't available from the default keyboard Thanks for the suggestion though :D

[mcgin]

Re: Conditional Combo Boxes in Excel - HELP NEEDED

Thanks :notworthy mcgin, after some intials trials with letters and numbers, vlookup seems to do the job. The way i do it (to get past any two combo box limitations) is to produce a unique number from a combination of the (for example) first two combo box values, and then the vlookup table looks for that unique value in the vlookup table (with an extra column containing the known unique values) and the third (fourth and five, etc) combo box is linked to the vlookup formula. simple! or perhaps too complicated? what do you think?

Sounds about right, twas what I had in mind when I suggested it, glad I didn't have to explain it :)