Drop Down Menu... again

[Kelly Armitage]

Ok I have created a drop down menu on sheet2, which takes data from an entire
list of parts from sheet1..... then with advice from here, used the vlookup
command to also fill out the serial number associated with what I chose from
the drop down menu. The problem is that each part might have 10 or 20 of
them with the exact same part names, and different serial numbers. No matter
which part i select, i always get the first serial numbers associated with
that part.. not the 3rd or 4th or 5th one even if thats the one ive selected
in the drop down menu. I have also dabbled with combo boxes but seem to get
no "control" tab that I am apparently supposed to.

Any ideas?

 

[Kelly Armitage]

I should probably also mention that the workbook is not locked or protected.
I am a little lost as to how i can accomplish this and i know its simple as I
did it 10 years ago, and just cant seem to remember how.

PLEASE HELP!

 

[Ragdyer]

Are you saying that you'll have 10 or 20 *exact* part names in your drop
down list?

How do *YOU* tell them apart when it comes time to make a selection from the
drop down list?

 

[Pete_UK]

Can't you modify your parts list slightly, so that if you have, say,
five entries with Part X, you make these Part X (1), Part X (2) etc.
or something like that?

Hope this helps.

Pete

 

[Kelly Armitage]

hmm no not really.... as an example there might be 20 identical notebooks ..
all called "Compaq 5000" with different serial numbers...... isnt there a way
for me just to reference the exact cell..... directly to the right of the one
ive chosen from the drop down menu... instead of using vlookup which just
takes the first instance all the time ?

i could just flip to sheet1, cut and paste the model, flip to sheet2, paste
the model, and repeat for the serial number, im just trying to eliminate the
repetition....

 

[Pete_UK]

Kelly,

you might like to look into dependent data validation, as illustrated
in this link:

http://www.contextures.com/xlDataVal02.html

This uses two (or more) drop-downs, the second one dependent on your
first choice. So you would only have one "Compaq 5000" to choose from
in your first list, then your second list will give you the 20 serial
numbers under that choice.

Hope this helps.

Pete

 

[Roger Govier]

Hi Kelly

If your part number - Compaq 5000 - is in column A, and serial number is in
column B, you could create a concatenation of the 2 in column C (or
elsewhere) with

=A1&" "&B1

Use this new column as the source for your dropdown list.