Excel experts: How do I make a drop down list?

Factual Questions
#1

Have I told you lately that I love you guys? I love you guys.

Now that you’re sufficently buttered up, please help me. I want to make a drop down list, tied to a cell, so I can just hit the first (or first few) letters of a word and it pops the word in that cell for me. (I’m working with employee’s service history cards, which I’ve created - each employee has their own workbook/file the way I’ve set it up. I’d have used Access, but I found it easier to set them up to print on the pre-printed cards using Excel.)

For example, each card has a race field and a sex field (cells, actually, but you know what I mean). So, I want to type ‘F’ in the sex field and have ‘Female’ pop into the cell, hit enter, and save me a few keystrokes. Type ‘B’ in the race cell and ‘Black’ will pop up, etc. These are the easiest two I want to do, but they’ll all be lists, just like you can set up in Access.

I’m currently using simple macros I’ve created for the small lists. The limitations of macros is I’ll run out of shortcut key letters for the larger lists and I (and everyone else who uses this form) will have to remember what shortcut key I’ve assigned to the thing they want. So macros are out.

Can anyone explain to me what I need to do or point me to the correct help keywords to figure this out? (I’ve searched on ‘drop down lists’, ‘lookup lists’, ‘lookup wizard’ and ‘write macro’.)

#2

Click on Tools go to Options, then Edit. Check Enable AutoComplete.

#3

My understanding of AutoComplete is that it looks for other entries in the same the column (in the current workbook) and pulls data from there. This is a seperate workbook for each employee, with only one entry of ‘female’ or ‘male’ in each workbook. I’m using a blank form for each new employee (and old employees right now, until I get them all entered), then doing ‘save as’ with their names. So there is no previous data to pull from.

Just to double check, I did go out there and look, and AutoComplete is already enabled.

I’m thinking I need to set up tables or something in my blank document and tie it to the cells I want to complete. I just don’t know where to start.

#4

Yes, AutoComplete is probably your quickest bet with Excel.

But I do want to make you aware that if you right-click on a cell and choose “Pick from list…”, a drop-down box appears, populated with existing entries from the same column.

#5

For a real drop-down box and not AutoComplete:

**Step 1: ** Somewhere on your sheet (it has to be the same sheet), type the list of choices for your drop-down, either in a column or a row.

**Step 2: ** Select the cells where you want the drop-downs, then go to the “Data” menu, and choose “Validation.”

**Step 3: ** Click on the “Settings” tab and choose “List” in the “Allow” box.

**Step 4: ** You will then enter the cell range of the cells you filled out in Step 1.

**Step 5: ** Click the “Close” button.

There ya go. You’ll need to follow these steps again if you add to your List Value cell range.

#6

You can format a cell with data validation based on a list. Click Data --> Validation and select ‘List’ from the ‘Allow’ drop-down, and you can select from a list of allowed values in a ‘table’ elsewhere on the sheet. (In some hidden columns, for instance.) This gives you a drop-down in the cell. However, it doesn’t give you auto-complete on those cells, like an Access combo box does. Still, once there’s some data around, the auto-complete feature should handle this for you.

#7

Alas, PoorYorick, you beat me by two minutes. I’ve gotta learn to type faster …

#8

That is so close! My only problem now is that AutoComplete isn’t picking it up.

I do have the drop down box now and can use the mouse if necessary. Is there a way to tie AutoComplete to it?

You guys are awesome, BTW.

#9

As SCSimmons said, AutoComplete works by comparing the values in the column. For instance, if “Wednesday” is already a value in the “Day of Week” column, once you type “W” in the drop-down box, “Wednesday” will automatically fill-in (if there isn’t already another value starting with “W.”)

#10

I forgot to mention that you can program combo boxes in Excel with VB to do damn near anything you want, but that’s a whole other kettle of fish.

#11

If you want Autocomplete to pick it up, I think you need to have the source list in the same column – and immediately above --your drop-down cell. That way Excel will think that your drop-down is part of the same series of data in the column and it will try to autocompete for you.

You can then hide the rows with the source list to make it look purty.

#12

Thanks, everybody.

This is the route I took. I’m gonna play with combo boxes/VB code sometime in the future and try to figure those out, so thanks for the info, PoorYorick.

I appreciate everyone’s time. This solution was so simple, I’m almost embarrassed I bothered you with it. :slight_smile:

#13

[Homer Simpson]D’oh!![/Homer Simpson] :smack:

New GQ: can anyone explain why I never, ever thought of doing that?

T’anks, BoS!

#14

This helps me with somehting I’ve been trying to do for a while! Do you know if there is a way to make data show up based on what you pick from the pull down box? I want to be able to choose, let’s say, flour, and have another column where calories appears.

#15

That’s easy. If you’re comfortable with fancy stuff in excell, look up the “vlookup” function in help and away you go!

If not …

Let’s say for example you have some foods and calorie values.

Make a 2-column table with food names in the left column and their corresponding calories in the adjacent cell to the right, like this:

Food | calories
Apples | 65
Beef burger | 100
Celery | 10
Pork fat | 300

Let’s assume our lookup table is in cells A10:B13

We can create a dropdown list in cell, say, C15 as outlined in the previous replies, telling it to pick from the range A10:A13.

To make cell E15 pick up the corresponding calories for the choice in C15, put this function into E15: =vlookup(C15,A10:B13,2,false)

That says: Look at the table in A10:B13. Look in the first column of that table for a match with the value in C15. Give a result of whatever value’s in the 2nd column of that same row in the table. False says the list isn’t sorted. If you do have a sorted list you can put true in there to make it work a little faster.

GOTCHA: Using true with an unsorted list doesn’t trigger any error message, it just gives the wrong answer!

You can also make a wider table, say of foods, calories and carbs like this:

Food | calories | carbs
Apples | 65 | 4
Beef burger | 100 | 2
Celery | 10 | 5
Pork fat | 300 | 0
Potatoes | 100 | 50

Then =vlookup(C15,A10:C13,2,false) gives the calorie value for the C15 food, while =vlookup(C15,A10:C13,3,false) gives the carb value.

Finally, there’s another version called hlookup that works the same but rotated 90 degrees. And I suggest you read up on the true/false setting a bit; it isn’t always quite as simple as I explained it.

#16

Thanks! I’ve been wanting to make such a spreadsheet for months, but couldn’t figure out the dropdowns. Now, I’ll be able to figure out nutritional content of recipes easy as pie!

#17

Actually, you can set it up so it doesn’t have to be on the same sheet (that’s what I normally do, especially if I have a lot of drop-downs, then hide the extra sheet). Switch to a different sheet.

  1. Type your values into a column.
  2. Select all of the cells with the values (and just those cells). In the Name box (the drop down box that has the name first cell selected in it - let’s say A1 in this case) type a descriptive name (no spaces!) - Let’s say we call our range of values “TestScores” and HIT ENTER on your keyboard. This is a must. You can’t just type a name in the name box and click away from it, you have to hit enter after putting in the name.
  3. Follow the rest of the steps PoorYorick listed. When you get to Step 4, instead of putting in the range of cells, you can just put in =TestScores (or what ever name you gave to that range). Your drop-down now uses the values you have just entered on a different sheet.

critter42

#18

Quick question - how do you set it up so that you can put the information on a separate sheet (as critter42 explained), but also use the vlookup as in LSLGuy’s example.

I’ve been fiddling around with it, and I can’t seem to get it to work.

#19

Actually, nevermind. I fiddled around some more and figured out how to pull information from one sheet to another. (I guess I can figure out more about Excel on my own than I thought. I am semi-capable in using Excel, but I don’t usually feel really confident with it).
What I did was (from LSLGuy’s example, set up a table of nutritional info on the first sheet in cells A1:C5. I also selected all that values in the Food column and named them “Foods” in the name box.

Apples | 65 | 4
Beef burger | 100 | 2
Celery | 10 | 5
Pork fat | 300 | 0
Potatoes | 100 | 50
On sheet 2 in cell A1 I made a drop-down box where the range of values for the list was =Foods. Then in cell A2 I put =VLOOKUP(A1,‘Sheet 1’!A1:C5,2,FALSE). Cell A3 is =VLOOKUP(A1,‘Sheet 1’!A1:C5,FALSE).