Drop down lists in excel

  tasslehoff burrfoot 22:45 14 Jul 2004
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.

Anyone help, please?


Many thanks

Tas

  tasslehoff burrfoot 08:51 15 Jul 2004

bump

  VoG II 16:58 15 Jul 2004

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 2004

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 2004

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.

Elsewhere on IDG sites

Galaxy Note 8 vs iPhone X

Awful clip art from 1994 is being tweeted every hour by a bot

iPhone X vs Samsung Galaxy Note 8

Les meilleurs navigateurs internet 2017