Creating a Linked Drop Down Menu

[Kelly Armitage]

Hello Everyone,

I have a spreadsheet, which tracks inventory on "sheet1" it has columns such
as "model" and "serial number" for example. On "Sheet2" I would like to be
able to create a drop down menu that lets me select one of the entries from
the "model" column on "sheet1". This I seem to already have accomplished and
it works fine, i can pick any of them from a drop down list. However what I
would like it to do autoamtically as well, is grab the applicable serial
number and put it in the next cell. I have done this before in an old
spreadsheet years ago, and cant seem to find a clear walkthrough for it. In
short I want to be able to select something from one drop down menu (already
done) but have it fill out two cells, including the model ive chosen, and the
serial number associated with it. Can anyone help, or point me to a
step-by-step?

thanks

 

[JE McGimpsey]

One way:

Assuming the dropdown is in Sheet2!A1 and the model and serial numbers
are in Sheet1!J:K:

Sheet2!B1: =VLOOKUP(B1,Sheet1!J:K,2,FALSE)

 

[Kelly Armitage]

this is almost very helpful, however due to setup differences, i keep getting
returned a value of "0"... no errors or anything.... so it looks like im on
the right track, however the only difference i can see, is that my "model"
and "serial number" cells are not side by side..... can you tell me what the
"2" and "FALSE" in that statement mean"?

 

[Kelly Armitage]

actually I played with that number a little and was able to get it.... it
does return the correct serial when selecting the part form the drop down....
now my only issue (very minor) is that when i have nothing selected in the
drop down... or it hasnt been used yet, that serial number cell displays #N/A
no biggy but its ugly, is there a way to correct that ?

 

[JE McGimpsey]

One way:

=IF(ISNA(MATCH(A1, Sheet1!J:J, FALSE)), "", VLOOKUP(A1, Sheet1!J:K,
2, FALSE))

 

[Kelly Armitage]

your first one worked (almost)... i also was able to solve the #N/A by
leaving some blanks at the bottom, and just selecting the "titles"

my problem now is, it seem no matter which model number i select, for
example if we have 5 of something.... no matter which one of those 5 I
select, it always gives me the same serial number which happens to be the
same one in the list....

any suggestions ? i have to get htis working

 

[Kelly Armitage]

correction, it always gives me the serial number for the first model in the
list, even if we have 5..
example if we have 5 "PART X" no matter which i choose ill always get the
serial number associated with the FIRST "PART X"

can u advise on how i might correct?