dropdown list, multiple sheets, automatic fill

**roybean** · 12-08-2009, 06:47 PM

Hi,

My level of functions in Excel extends itself to using simple functions as IF, however I do have basic programming skills (Java, Pascal etc) and I know how to make dropdown lists.

Is there an elegant way to have within the one and the same Excel document, have one sheet (sheet 1) with input in column format, on a 2nd sheet (sheet 2) have a dropdown list (composed of the input on the 1st sheet), and when an item is selected from the dropdown list the cells to the left (still on the 2nd sheet) be filled with information based on which item was selected in the dropdown list?

I attached an example that shows what I would like to do.

I did manage to do this but with everything on the same sheet, and I used a very long and ugly string of IF functions. I'd need one IF for each input, so 80+ IF statements.

Any help is appreciated, even if just to get me looking in the right direction.

Tnx

Roybean

**khamilton** · 12-08-2009, 07:49 PM

From the looks of it use this in Sheet 2 B2: =index(Sheet1!B:B,match(A1,Sheet1!A:A,0))

**teylyn** · 12-08-2009, 08:24 PM

I'm not quite sure whether your question is about the dropdown list, or the lookup aspect.

For lookup, as an alternative to khamilton's suggestion you can also use

=VLookup(A1,Sheet1!$A$2:$b$3,2,false)

If you want to learn about dropdown lists, read about data validation here:
http://www.contextures.com/tiptech.html , scroll down to the data validation section and start with the intro.

hth

**roybean** · 12-09-2009, 05:39 AM

Thanks both of you!

I tried your solution teylyn and it is sweet

Just what I was looking for. I have a Swedish version of Excel so the name of the function was different and the , are instead ;

To any Swedes out there who are looking to do the same thing, here is the Swedish version:

=LETARAD(A2;Blad1!$A$2:$B$3;2;FALSKT)

I am going to try your solution now khamilton.

Again thanks!