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

 

[maxflia10]

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
	A	B	C	D
1	smith	555-5555	555	street
2	jones	666-6666	666	avenue
3	king	777-777	777	highway
4
5	jones	666-6666	666	avenue
Sheet1

 

[DonkeyOte]

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]

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]

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.
This message was edited by maxflia10 on 2002-09-07 16:46

 

[Exzel]

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]

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]

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]

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]

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