Drop down boxes

[Exzel]

How do you create drop down boxes?

For example, i want to be able to pick from 3 sales reps: jones, smith, and clark.

When I pick one (for example "Smith") his information( for example phone number and address) will appear on another workbook tab.

I have heard of a VLOOKUP command (but I am not familiar with it)Do you use that?

Lastly, How do you make a workbook tab hidden and unhidden?

I wonder if these questions will stump many people??

Thanks all for your help!


Exzel

 

[Exzel]

Maxflia10,

I am confused once again...

I am having trouble as you see above to paste my sheet here..so I will describe my worksheet.


A B C D

1 SMITH 555-5555 555 STREET
2 JONES 666-6666 666 AVENUE
3 CLARK 777-7777 777 HIGHWAY


And at B5 is my dropdown box with the 3 sales reps.

I then, named B1:D3 "mydata"


First, where do I type the Vlook command?(Is B5 ok??)

I typed: VLOOKUP(A1,mydata,2,0)

I received an error #N/A.


OK, what did I miss here??

Also, in your original example you used "$", why??



Also, you typed:

"=VLOOKUP(B1,range name,2,0)

D1 is the lookup value or where you're sales rep names will appear in the validation list. "



Did you mean "B1" is the lookup value???


You have been a tremendous help so far!!

Exzel

 

[maxflia10]

Exzel,

Yes I did make a mistake, which I corrected.

If your drop down list is in B5, enter the formula in C5 as:

=VLOOKUP(B5,MYDATA,2,0)

In D5, enter

=VLOOKUP(B5,MYDATA,3,0)

In E5, enter

=VLOOKUP(B5,MYDATA.4,0)

Now go to B5 and select a sales rep and post back what happens.

 

[RichardS]

In your VLOOKUP formula, replace A1 with B5, being the cell which has the drop down list. This will have the sales reps name, and VLOOKUP will find that name in mydata, and return the second column in that range, or the phone number.

The $A$1 is an absolute reference. If you copy it somewhere else, it will still refer to $A$1. If you leave the $ signs out, the reference becomes relative, so if you copy the formula down one row, it will become B1.

HTH

Richard

 

[Exzel]

I jUST RECEIVED THE ERROR #N/A.

What do I do wrong??


Exzel

 

[Exzel]

I reviewed the worksheet again and got it to work.

Ok I have a few questions:

How do I hide all of my data, so the user doesn't see it and only sees the drop down boxes?


Is it possible to put this data in a hidden workbook? How would I hide and unhide it?

 

[RichardS]

You could hide the columns or rows your source data is, using Column|Hide or Row|Hide from the format menu. You could also have your source data on another sheet oin the same workbook, and hide that sheet using Hide from the Windows menu.

Richard

 

[slam]

this is exactly what i want to do only i need to have my drop down menu on a seperate sheet from my data.

i can't seem to get the first step to work - the part when the drop down menu is made by selecting validation from the drop down menu etc. it won't let me switch to a seperate sheet to select a range of data. can anyone help?

 

[Brian from Maui]

Slam,

You need to name your range, then in Data Validation use Custom/Formula and enter
=rangename, where rangename is whatever you named your range

 

[slam]

I still can't select a seperate sheet for my datarange nor can i just type in =list in the formula field without getting an error message 'the named range you specified cannot be found'