drop menu

[CC40457]

hello, newbie here.

i have a spreadsheet that is used internally for a fantasy road racing league, it is fairly basic and some of the stuff on it is probably at the top end of my capabilities with excel. i am wanting to create a drop down menu that contains all the data from one and then fills it in the next sheet when selected on a drop down. i know that doesn't make much sense so i have added some parts of it in the attachment. if i get this sorted i will probably have more questions as i have a few more ideas above my station lol.

FRR.zip

[Jazzer]

Hi there,

Because the lists you want to use are on an other sheet (which is good), you cannot use Data Validation -> List directly. You must first define a name to the area where the lists are.

1. Select the area RIDERS!A3:A23 and give it a name. In the area above the column name "A" write something like moto_gp_code and press enter.
2. Also it is a good practise to name the lookup area. So select RIDERS!A3:D23 and name it moto_gp.
3. Select the area TEAMS!B9:B10
4. Go to Data -> Validation...
5. Select "List" from Allow: and in Source: type =moto_gp_code and press OK.

Now, make the lookup code, but first format cells C9:D10 as General, so that the formulas will work.

1. Type in cell C9 =VLOOKUP($B9,moto_gp,2,FALSE)
2. Copy that to D9:E9
3. Change the formula in D9 and E9, so that the VLOOKUP will return the right column. (in D9 change the 2 to 3 and in E9 to 4)
4. Copy C9:E9 to C10:E10

that should work.

- Asser

[CC40457]

thanks! it all works fine and makes it easier to use instead of maually inputting a lot of text. just wondering if you could explain what the formula means though, just for understanding purposes.

[Jazzer]

Hi and you are welcome.

You mean the VLOOKUP-formula? In Excel's help it's explained quite clearly, possibly much better than I could explain it here, so I recommend that you read it from there.

- Asser