# Drop down lists in excel

tasslehoff burrfoot 22:45 14 Jul 04
Locked

Hi

Following on from all the help I received from my last query, I now have the excel bug and am thoroughly enjoying playing around with it and learning how to use it.

However, I have come across something which I just can't figure out:

I have a drop down list with 8 options (e.g. team1, team2 etc) what I want is to have another drop down list, the options of which are dependant on what has been selected in the first list.

For example if team 1 is selected in list 1, players 1-5 appear in list 2; if team 2 is selected in list 1, players 6-10 appear in list 2.

This must be possible but I just can't figure it out.

Many thanks

Tas

tasslehoff burrfoot 08:51 15 Jul 04

bump

VoG II 16:58 15 Jul 04

You need to have a formula to provide the input range to the second input box.

As an example, I've used the "names" A-J in A1:A10. (This is for 2 teams of 5; I'm too lazy to extend this further).

My first input box has a choice of 1 or 2 and its answer is returned in B2.

Then in D1:D5 i have the formulas

=OFFSET(A1,(\$B\$2-1)*5,0)

=OFFSET(A2,(\$B\$2-1)*5,0)

etc.

D1:D5 is specified as the input range for my second list box. So I get a choice of A-E or F-J depending on the selection in the first box.

Simsy 19:20 15 Jul 04

Cell A1 contains the word "England", Cell B1 contains the word "Scotland".. the Range A1:B1 is named "Nations"

Cell Range A2:A5 is named England, the four cells contain names of English cities...

Cell Range B2:B5 is named Scotland, the four cells contain names of Scottish cities...

Cell B10 is named "Country". It has data validation applied... select "List" and in the box below put =Nations

Cell B11 has data validation applied... select "List", and in the source box below put =Indirect(Country)

Now, when you select cell B10, (named "Country"), you have a drop down available with the possible entries "England", "Scotland"

If you select "England", the entries in the cells A2:A5 will be available in the drop down that is in Cell B11.... If you select "Scotland", the entries in the range B2:B5 will be available...

I think this is what you want, but beware that all this does is make entries available to you... it doesn't do any calculating...

So, for example, you could choose England, in B10, and then "London", (assuming this was one of the entries in the range), in B11.... You could now go back to B10 and change that entry to "Scotland" and "London" would remain in B11.

Note also that for this to work the names of the ranges, (England, Scotland), must ALSO be the CONTENTS of cells in another named range, (Nations)

I hope that makes sense!

Regards,

Simsy

tasslehoff burrfoot 22:51 15 Jul 04

Once again, thank you both very much!

I will hava play with these, I'm sure they are both what I need.

Thanks again

Tas

This thread is now locked and can not be replied to.

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

This abstract video touches on division in our technologic world

Best alternatives to iTunes for Mac | Best music players for macOS: Free your music from the…