Drop down box or list

C

Capt. Andy

I have been getting pretty good with excel but there are things that I just
do not understand.

I have a cell that I enter a name. Some of the names are long and I would
like to make the entry quicker. What is the best way? I think it is a drop
down box but I just do not understand how to get started. Do I make a list?
What? Help!
 
L

L. Howard Kittle

A drop down would work. Go to Data > Validation > Settings > Allow > List >
Source > Select the list of names > OK. Or for the Source you can enter the
names in the Source box separated by a coma (no space) or you can name the
list you have on the worksheet someplace, say name it MyList and then in the
source box enter =MyList.

HTH
Regards,
Howard
 
C

Capt. Andy

OK! I have my drop-down list working. Now, each name in the drop-down box
(these are boats) has a specific number attached to it (official vessel
number). I would like for that number to pop up in another cell when the name
of the vessel is entered. I tried insert>name but am unable to make it work
as yet.
 
L

L. Howard Kittle

Hey Skipper...Capt.

Looks like you need to do a Vlookup and a list of the boat names in one
column and the boat number in the next column. Your vlookup formula would
look something like this...

=VLOOKUP(A1,F1:G20,2,0)

Where A1 is where you have the dropdown
Where column F is the list of the names of the boats
Where column G is the list of the respective boat numbers

HTH
Regards,
Howard
 
C

Capt. Andy

Thanks, Howard,
Now I need to be able to attach a number associated with the name in the
drop-down in another cell. I have been studying hard and I am thinking the
VLOOKUP function might be the way to go. In other words the name in the
drop-down is
"M/V North Wind" and her offical number is "123456". North Wind was in the
drop-down and in the ajacent cell her official number would pop up. I have
hundreds of boats that I am dealing with. I realize that I could just add the
number to the name when I enter the name for the drop-down but it is not as
clean looking. And not nearly as cool.

Thanks for your help,
Capt. Andy
 
C

Capt. Andy

Howard,
It is all working. I thank you very much. I probably woul have taken me
forever to get the formula correct. Now all I have to do is do an 'If'
stament to get rid of the #N/A. Also in all my looking around I thought I saw
somthing about about getting to the name in the drop-down quicker. At this
time I have 158 names and it is still growing. That is lotsa scrolling.

Again, Thanks
Capt. Andy
 
L

L. Howard Kittle

Maybe you are thinking of this Poor Mans version of Auto-Complete of Data
Validation...

I am not sure who to give credit for this, I'm pretty sure I did not invent
it but it works.

With your list do a sort descending.

Then at the top of the A's insert an A, (probable have to enter a row) same
with the B's and on to the Z's. So now your 158+ list is sorted with the
leading letter of the list at the beginning.

Now click in your Validation list cell down arrow and enter an "s" and DO
NOT hit enter... leave the cell in the edit mode, the cursor will be
blinking.

Now click on the down arrow of the drop down and you will be taken to the
beginning of the "s" list. So now you are down to the top of the "s" list,
(probably the stuffy sail boat entries..LOL)

Scroll down from here to your selection and hit Enter.

And try this with you VLOOKUP formula. Change "Boat Sank" to your
description, perhaps "Got Lost"

IF(ISNA(VLOOKUP(A1,F1:G20,2,0)),"Boat Sank",VLOOKUP(A1,F1:G20,2,0))

HTH
Regards
Howard
 
Top