Creating a Drop Down List in Excel

This was very easy to do and I did it with ease, EXCEPT I came across one problem nobody seems to be addressing. I’m trying to do a drop-down list that does not appear elsewhere on my spreadsheet. I typed the list specifically FOR the drop-down. Therefore, when I delete the items I typed, the choices in the drop-down disappear as well. Is there a way to remedy this?

You can only hide them from the sheet or put them in another sheet

You can label your cells and do a drop down list from that.

If you select the cells with the information you want as a drop list then goto the area that shows what you have selected (if you slected the first cell on the page it would say A1)

Type a label (mine was Name)

Go to validate data on another worksheet and type in the area where you would pick the cells and type

=Name (this was my label)

it will create a list from your labeled coloum or row.

Hope this is clear it was a bit tricky to exlpain.

TTFN
Nick

You can create a list where the data is not located on your spreadsheet.
Click on Data
Select Data Validation
On the settings tab select “List” from the “Allow:” drop down
In the section marked source type in your list separating each listed item by a comma.

Perfect! Thanks. Exactly what I needed.

Thank you Trena! That worked PERFECTLY!!! I am doing the Happy Dance!!

Hi,
I am not able to create a drop down list from another reference sheet.able to only in same sheet,please help me out of this problem.

Hi Kapil (and the rest) – you probably already found it, but:

the syntax is =Worksheetname!range

Worksheet name – Sheet2
range = A1:A6
range2 = C1:C10

syntax in source:

=Sheet2!A1:A6,C1:C10

Hi Kapil, I did not have that syntax so thank you very much. That helped the first part of my problem.

My 2nd question is: can my ‘list’ be formatted colored cells (no text)? I am doing a green-yello-red status. I formated each of the cells for red, yellow and green. but when I set up the validation, the list shows up as if the options should be there, but they are blank. Any idea of how I can have my list be cell colors?

I need something a bit different. My problem…… I have 2 columns. Cells A1 through Axx contain the product description. Cells B1 through Bxx contain the product cost. I can easily create a drop down showing the descriptions, but when I select the description (col. A), I really want to use the corresponding cost (col. B) to calculate the total product cost.

Think of it as making a pizza…… I select the product from col. A (crust, sauce, toppings, etc.) but I want to use the corresponding cost from col. B ($2.00, .65, .35, etc.)

I’m sure it can be done, I just don’t know how.

I’m not a programmer.. Thanks.

Hi Mary,
I am only a beginner, so my thoughts on your dilemma may be amateurish and crude, and I may be shot down in flames for it, but how I would address the colour issue is to have my data validation list contain the following values:
Red, Blue and Yellow. Then the cell in which I wish this displayed would have the following conditional format rules
if cell value is equal to red (then make it display red)
if cell value is equal to Blue (then make it display Blue)
if cell value is equal to Yellow (then make it display Yellow)

Thanks, Great Help

Mary,

Another thing you can try is conditional formatting. It’s under the Home tab in the style section. Select the range that you want to turn different colors, and then select the appropriate conditional formatting, and you can make it turn colors based on dates, values, equallity and several other things. It’s pretty easy to get the hang of

Using my named list and data validation and love it. My question is, how can I select something from the list without having to use the mouse to drop down and select and then tab on over to the next field?

Amy:

If you are asking how you can use the keyboard to open the drop down list you can use the key combination of Alt + the down arrow key

When I type my data validation list on another sheet it works. but when i save and close it then reopen, only the data validation drop down is there if i hid the list on the same sheet. If the source was on another sheet it does not save. please help!

how can i create multiple drop downs?

I haven’t been able to figure out how to accomplish this – Y have a “master page” worksheet that includes a list of account names which correspond to individual work sheets for each account. Is there a way to click on the cell on the master page that includes the account name and have it send me to the corresponding work sheet?

when a make a list consists of different rangs(i.e. B4:G15)
the below massage appeared:
“The List source must be a delimited list, or a reference to singl row or column”

Iam going crazy…. i realy need to make a list consists of different rangs or cells in the same worksheet or another sheet….. HOW CAN I DO THIS !!!!!!!!!!!!!!!!!!?????

I can’t go to Data Validation. It’s grayed out. What to do??

I have created a list and now I need to add the different parts of the list for totals. for example the list has different excercises. R for running, b for biking, c for cycling, etc. I want to add up how many times I did each activity throughout a given week. Any ideas how to do that?

I have an excel spreadsheet containing dropdown lists on another hidden sheet. Question is: I can change the font color in the dropdown lists as well as bold the font but when the dropdown list is displayed on the worksheet, the font color is not displayed and/or bolded. I tried the conditional formatting but this does not work as well.

I am trying to make a drop down list that contains text. But once the text is selected I want the selection to display a number. Please help.

Thank you Trena! That worked PERFECTLY!!!

I have a column with a pull-down list (tied to a named List). The column is only 4 characters wide, but the when I pull down the list, the window is much wider. Is there a way to control the width of the list?

Thanks a lot trena, your tips were highly simple and easy to understand. Solved my dilemma in a jiffy

Thanks.it is very usefully…..good job..

If i have 3 columns, 1 is data (Matthew, mark, luke , john) and the 2nd is (1,2,3,4) … and the 3rd is (First, Second, Third, Fourth)… can i create a dropdown for the 1st (Names), that when one name is selected, it fills the remaining associated cells of info in the other 2 columns beside it?

Use validation list on the first column, then use VLOOKUP to select data for 2nd & 3rd rows.

thanks Trena

Thanks Trena… It helped me !!!

I’m not sure how to allow multiple selections when choosing items from the drop down box. I want to be able to select one of more choices from a list. Please explain how to a novice at creating drop down boxes.

I have added a drop down list to a spread sheet my security officers use on a lap top to make entries during there patrols. They make allot of different entries during there shifts. I made a drop down list with a handful of commonly used tasks. This just saves them typing time. Is there a was to allow them to type other entries on the spreadsheet instead of selecting whats in the pulldown? When I try to type something else then whats in the pull down, I get an error message. Thanks

Use the Error ALert tab and uncheck the Show error alert.

Same question as Sharon, how do I create a dropdown list and allow selection of multiple entries?

I have created a drop down list in a hidden sheet that I constantly update. How do I set it so when they attempt to key data in they get a warning to select from the drop down list?

Mary I have the same problem. Did you find any solution ?

how do i add a name to a drop down list but can’t find the orignal drop down list which is feeding this?

I’ve created a drop down list. I need to include ten items in the list which are aspects of patient care. Each of the ten items are sentences that contain several words (i.e. Timeliness and appropriateness of diagnosis). After typing in the 4th or 5th aspect in the drop-down list ‘source’ box, it will not allow me to go any further. It’s as thought the capacity of the drop down list is limited.

Is there any way to increase the capacity of what can be entered in the drop down list?
Any other recommendations appreciated.

How can I make the validated list be sorted A-Z and not presented in the same order as it is in the list – without sorting the actual list itself. Is this possible?

Amazing – at last someone who gives an undertsandable way of doing things and it works!

I want a drop down list that references a list I have in another workbook (whole other excel file). Is that possible? How?

Thanks terna

Worked great, thanks for letting me know how

Hello,

I know how to create a drop down list. My dilemma lies here; when I make a selection from my drop down list I would like to have that selection pull up different choices that is tied to that selection.

(Example) Drop Down List:
Red
Green
Blue

I chose the color Red. Now I would like a list of all Red Items like apple, cherry, car (all things tied to Red) to appear in the neighboring column.
I chose the color Green. Now I would like a list of all Green Items like grass, money, trees (all things tied to Green) to appear in his neighboring column.

Is this at all possible in Microsoft 2007? If so can someone please help me with step by step directions. Much appreciated! Thank you.

Thanks alot Trena

Can you creat a list and when you click on a choice on the list it takes you to a different area of a spread sheet? I am working with a very large workbook and want to be able to quickly go to certain areas of it from the first page. Thanks.

thank you for giving good knowledeg