Drop down boxes

Exzel

New Member
Joined
Sep 5, 2002
Messages
27
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

maxflia10

Well-known Member
Joined
May 20, 2002
Messages
890
Exzel,

Drop downs are part of Data Validation
In a column list your sales rep. and name that list. Go to Data/Validation, in the allow box, select List, in the Source box enter =name(whatever you named your range)

For VLOOKUP follow the example below:
Book2
ABCD
1smith555-5555555street
2jones666-6666666avenue
3king777-777777highway
4
5jones666-6666666avenue
Sheet1
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
If you want to hide a worksheet normally (i.e. not code)

Format - Sheet - Hide (select sheet)

To do through code...

Sheets("sheet1").visible = false
 

Exzel

New Member
Joined
Sep 5, 2002
Messages
27
On 2002-09-06 20:04, maxflia10 wrote:
Exzel,

Drop downs are part of Data Validation
In a column list your sales rep. and name that list. Go to Data/Validation, in the allow box, select List, in the Source box enter =name(whatever you named your range)

For VLOOKUP follow the example below:

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx


I am still confused!


I do not have much experience with the Validate or Vlookup command.


You are only showing the scroll box outside the worksheet. How do I get the Scroll bar in the worksheet. How do I get the scrollbar to show a choice (for example: Smith, Jones, Clark)? You are only showing cell references in the scroll bar.

How is the list named?(one of the first steps that you mentioned)

Where is the VLookup data stored? How do you edit it? (Is it hidden?)

Any other information that you can provide would be appreciated, especially since these are very, very new commands to me!

Thanks!!

Exzel
 

maxflia10

Well-known Member
Joined
May 20, 2002
Messages
890

ADVERTISEMENT

Exzel,

OK, let's take this one at a time. We'll do the drop down first.

In A1 enter the first sales rep. In A2 enter the second...etc.
Now highlight A1 till the end of your list. In the window left of the formula bar, type List and enter. Or go to Insert/Name/Define and enter List in the Names in workbook window.
Click on a cell you would like the drop down to appear say B1.
Now go to Data/Validation and in the Allow window, select List and in the Source window type in =List.
Now when you click on B1 you should have a button on the right corner of the cell, click the button and your list will appear.
After you've done this, post back your results and we'll do the VLOOKUP. :biggrin:
This message was edited by maxflia10 on 2002-09-07 16:46
 

Exzel

New Member
Joined
Sep 5, 2002
Messages
27
maxflia10,

Ok..followed your step by step directions.

This step works. I now have at B1 a dropdown list with my 3 sales reps. (If you want to see my actual results, how do I show you this here?? I can not just paste it here!))

I am ready for the next step.. the VLOOKUP...


Thanks again for your patience and help!!


Exzel
 

Exzel

New Member
Joined
Sep 5, 2002
Messages
27

ADVERTISEMENT

maxflia10,

As a follow up to the last post:

I see the Excel addon on this board that gives me the ability to post my spreadsheet.

I tried to use it, but when I paste or I get is Java words without any pictures (on my desktop I see what should be going in the clipboard) sny thoughts how to correct this?

Exzel
 

maxflia10

Well-known Member
Joined
May 20, 2002
Messages
890
Exzel,

VLOOKUP work with columns. The left most column is the lookup column, in you case that would be the sale reps. In the cell adjacent to your sales reps name, enter the phone and the next adjacent cell address. So you would have in A1 sales rep one, in B1 his/her phone number and C1 the address etc. Now highlight the columns containing the information and name it, just like the data validation list. This is the syntax for VLOOKUP:

=VLOOKUP(D1,range name,2,0)

D1 is the lookup value or where you're sales rep names will appear in the validation list.
Range name is the name you gave for all the information ie. A1:C4.
2 is the column number from where you want the information reutrned.
0 means false or exact match and if no match is found a #NA will be returned.

Now go back and see the sheet I posted earlier! Hopefully you'll understand. :)
This message was edited by maxflia10 on 2002-09-08 19:56
 

Ivan F Moala

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209
On 2002-09-07 21:22, Exzel wrote:
maxflia10,

As a follow up to the last post:

I see the Excel addon on this board that gives me the ability to post my spreadsheet.

I tried to use it, but when I paste or I get is Java words without any pictures (on my desktop I see what should be going in the clipboard) sny thoughts how to correct this?

Exzel

Hi Exzel and Welcome to the board.
Have a look @ the Readme file avial from the
dropdown list of the Addin.

Postback with any problems.
 

Exzel

New Member
Joined
Sep 5, 2002
Messages
27
Ivan F. Moala:

My problems with HTML Maker:

I can not read the "readme file".

I received the error:

Run time error 1004

Cannot start the source object for th object



Also, when I start Excel I receive the error:

"Could not load object because it is not available on this machine"

Can you help me with my problem with this program??


Thanks!

Exzel
 

Watch MrExcel Video

Forum statistics

Threads
1,130,342
Messages
5,641,592
Members
417,224
Latest member
llama9207

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top