Free Microsoft Excel 2013 Quick Reference

Assign Values to Drop Down Menus

I am trying to figure out how to assign values to a cell based on the anser in a drop down menu and a seperate factor. Here is what I am trying to do:

For different counties, building permits cost different amounts. Also, within the counties the price is different based on the size of the building. So a 3 bedroom house in County A will be a different price than a 3 bedroom in County B. Also a 3 bedroom house in County A is a different price than a 4 bedroom house in County A.

If I select county A in my list, and input the number of bedrooms of the house in an adjacent cell how can I make the correct value come up in a third cell. Say in County A its $500 per bedroom. If I select County A in the dropdown and input 3 bedrooms, how Can I make the third cell say $1500? I also need to assign different values based on different answers in the drop down. Say in County B its $600 per bedroom. I need the answer in my third cell to say $1500 if I choose County A and $1800 if I choose COunty B and so on.

Any help would be greatly appreciated. I am LOST!!!


I'm looking to add values to drop down lists. I want to be able to select a product from a drop down list and then it will update rows with values such as: unit price, unit buy price, unit sell price etc

i will attach a spreadsheet so you can see what i mean. the drop down lists are on the left.

Any help very much appreciated

I'm not even sure if Excel can do this...

I have a worksheet with 5 different drop-down lists on it (each with several
options to select from). I want to be able to assign each option a value so
that I can create a cell/formula that will average the values selected.

Can I do this in Excel? How????


I'm using the HYPERLINK function and finding it reliably useful. I'd value being able to assign hyperlinks (somehow) to individual menu drop downs(validation) in the other sheet.

I attached example file. (when i click on ice cream in main tab, I want to see ice cream in the drop box tab as well)

Hi There,

I have a dependent drop down list, whereby the 2nd column is dependent on what is chosen in the first column. I want the option to enter in my own values in either column if none of the options in either list will be used.

I've attached the spreadsheet to look at.

Sheet 1 is my data, Sheet 2 is where I want the "Blank" option to be able to be customized

Thanks so much.

Is there an Excel guru that can help with this - its related to "drop down menus"

I have 2 colombs of data.

AT the bottom of the first, I have created a drop down menu using the "data validation" feature in excel.

At the bottom of the second colomb, I have used an "IF" function that returns a result, which depends on what value is chosen from the drop down menu in colomb 1

The problem I have is that I want the TRUE result from the IF function to be another drop down menu, being the data in colomb 2.

How do you write an IF function where the TRUE result is a drop down menu??
I tried to create a drop down menu of colomb2 elsewhere in the spread sheet, and used that cells location as the TRUE value, but this didn't work either...

ANy suggestions??

How do you get a list of predetermined values to drop down in a list for a givin cell in Excel. Ex. Click on cell A1 and it gives the choices of Yes or No

Please help

I have a form already made, but cannot figure out how to add/remove attributes to drop down menus.

any way i can post this or attach this excel file for you all to check out and help point me in the right direction?


Is it possible to assign values to names in a list, so that when you validate
it as a drop-down list, you can select a name from the drop-down and it's
corresponding value will be added to separate cell? Basically, I have survey
questions which have five possible responses: Excellent - Very Good - Good -
Fair - Poor. We want to assign a value to each, 5 for Excellent, 4 for Very
Good and so on, so that when a response is selected from a drop-down, it's
value appears in a separate cell (so that we can calculate a total and
average score from the selections).



Trying to get my head around drop down menus and was wondering if there was a way to filter them.. autofilter is not an option.. I have the following illustrative list for category, subcategory and material. There are thousands of materials and each is assigned to a subcategory which in turn is assigned to a category.

I was wanting it so that if I were to select a category only the subcategories assigned to that category will show in that drop down menu and in turn the same thing for materials... it's just that with thousands of materials it would make it a LOT quicker to select the relevant material.

Can't really use Autofilter as it is for many different users and is for a dashboard I am wanting to send out.. with graphs etc so need to select a value rather than filter down to it.

I have no idea how to use VBA and I don't really know how to use many advanced features in Excel (kind of on the I just learned how to use Vlookups and so am gonna use them anywhere I can kind of level)

Many thanks in advance for your help!




I’m trying to make a cost estimate program for a pipeline. As a basic feature I would like to use drop down menus to be able to select a pipe from a list of standard ones, and then to enter the length of pipe in another box and to have as a result the cost. So the drop down menu will need to have text to select from (pipe size) and have hidden behind this a cost per meter. This will then be multiplied by the length to give the total cost.

I am new to advanced use of excel (i.e. VB) and have been playing around with ListBox and ComboBox for a few days but can not even add data items to these in an excel sheet. How do I permanently populate a drop down list in an excel sheet and have each item have an embedded value. How do I then access this value to perform calculations with?

Ideally I would like to have the source data for the drop down menu in another sheet for easy updating i.e. the pipe sizes in one column, with the cost in another so that users can easily update these values as market prices change.

Could someone please help me out or point me in the right direction?

Many Thanks,

Hi folks,

I'm new here (and to Excel as well!) and would appreciate some help.

I'm trying to add numerical values to the text that I have put in a drop down list, I had a look about and followed the advice given in this thread. However, as soon as I put more than 3 entries into a formula like this:


It returns a #N/A value for most of the entries, and does not assign the correct values to the text.

I've checked that all the spelling etc. matches, and am at a bit of a loss. I'm trying to use a list of 14 names, and assign the relevant points value to them. It's for creating a homebrew Army Builder.

Many thanks

Hello, I'm creating a user interface for querying data records that primarily uses drop-down menus.

The data being queried includes many columns and thousands of rows, and creating named ranges for each drop-down menu would take too long, and would require monthly updating. For these reasons, I'd be grateful for assistance with creating a VB script that could be assigned to a drop-down menu which would display only the unique records for that column, based on a sequence of user-selected drop-down menus.

The data is structures per the following example: Region (North, South, etc.; 6 unique regions); Market (Boston, Los Angeles, etc.; over 100 unique markets); Segment (Fast Food, Formal Dining; 2 unique segments); Company (Arby's, McDonald's, etc.); Offer Type (Entree, Drink, Entree/Drink Combo, Dessert, etc.; 8 unique offer types); Entree Type (Hamburger, Cheeseburger, Chicken Sandwich, [blank] for non-meal records; over 50 unique meal types.

I want to create sequential drop-down menus that display alphabetized results within the sub-set of filtered records, per the following example:

-The Region drop-down menu displays all 6 unique region records for the entire record set (North, South, etc.).
-If Region selected="West", the Market drop-down menu displays unique market records only for the West region (Los Angeles, San Francisco, Seattle, etc.).
-If Market selected="Los Angeles", the Segment drop-down menu displays unique records only for the Los Angeles market (so if no segment records exist for "Formal Dining", only "Fast Food" would display).
-If Segment selected="Fast Food", the Company drop-down menu displays unique records only for the Fast Food segment, filtered for Los Angeles (Arby's, Del Taco, etc.).
-If Company selected="Del Taco", the Offer Type drop-down menu displays unique records only for Del Taco, filtered for Los Angeles/Fast Food (Entree, Drink, Entree/Drink Combo, etc.).
-If Offer Type selected="Entree", the Entree Type drop-down menu displays unique records, filtered for Los Angeles/Fast Food/Del Taco/Entree (Burrito, Taco, etc.).

To sum up, this sequence of user-selected drop-down menus would filter out records for unrelated records, such as hamburgers at McDonald's in Boston, pitas at Falafel King in Minneapolis, and so on. describes what appears to be part of the solution, but I'm such a VB novice that I don't know how to render that declaration into a drop-down menu, much less how to apply it to so many sequential drop-down menus. As mentioned above, using named ranges and INDIRECT in data validation would require dozens of work hours every month, so the solution would need to be automated by VB.

Thank you for reading! Any assistance would save my skin (and maybe my job). I also welcome fee-based help offers that could help out in a pinch.

ok 2 questions actually... I'm new to excel without any training in it whatsoever and I'm wondering... 1.) how do a set a cell to be = to the value selected from a drop down menu... and have the cell display the exact value, ie. "Bob" "Dave" or "Suzy" rather than 1, 2, or 3??? 2.) How can I set excel so that if I Insert a row in somewhere it won't throw off any formulas I already have programmed in below the inserted line? I haven't looked into this one yet, i dunno if it's a valid problem... some guy in my office mentioned he was having trouble with it though... and I'd like to give him a hand... Thanks a ton to anyone who can help me out here!

I was hoping somebody here might be able to tell me if what I'm trying to do is even possible.

I have created a drop down menu wseveral items for the first time. The drop down is located on several worksheets. What I'm trying to do is find out if there is a way to do 2 things.

A) capture a total (numerical value) of the number of items selected from the list on each worksheet. ex. if on worksheet #3 15 cells contain data from the drop down cell A1 on worksheet #1 would = 15.


B) capture a total (numerical value) of the number of times a specific item was selected from several worksheets. ex. if ABC was selected from the drop down menu 8 times on worksheet #2 & 6 times on worksheet #3, cell A2 on worksheet #1 would = 14.

Is this possible at all?

Hello everyone!

I've run into a problem I am having a difficult trouble figuring out and I'm not even sure if it's possible to do. If someone could point me in the right direction I would be greatly appreciated.

I have a series of names (in say A11-A30000 on sheet "Main") and I would like to set it up so that when the user clicks on the cell it will go to another worksheet and carry the value of the cell over into a drop down box (ex: take data inside A11 and place it in B2 on "Sheet2").

Is this possible to do? I know I can use hyperlinks to move the user from one sheet to another on-click, but can I also carry over values? If I am unclear in what I'm asking please let me know and I will try to explain better.

Hi there,

I hope I put this so its easy to understand so here goes.

I want to make a drop down list (easy, Done)

But this is the hard part I cant work out. I want to be able to have other
values attached to the drop down list that appear in other cells. eg. below.

In Cell A1 is the drop down list. And I want to add values to the items in
that list that show up in the cells C1-C8 depending on what it makes.

in the drop down list say
wooden box
wooden barral

So for wooden box I want to have in C1 2(for the number of planks needed
in C2 I want 20 for nails
in C3 1 for a handle

But when I choose the barral from the drop down list I want it to say
10 in C1 for planks
50 in C2 for nails
0 in C3 for the handle

I know it can be done as I have seen a sheet like it but its protected with
a password and I dont know whos it is.

So can anyone explane how to do it, if needed I can try and explane better
with a pick or put the sheep up so you can see it.

My book contains 2 sheets. The second sheet is the following format:


And the list continues with text in column A and values in Column B for
many, many values.

In the first sheet, I want to have 2 drop down menus. They would be
side by side for the sake of comparison. In each menu, one is able to
see all of the different texts that are in column A in sheet 2.
Therefore, one may choose 'abc' in one drop down menu, and 'def' in the
other. Then, I would like the respective value from column B (sheet 2)
to display below the drop down menu.

So in Sheet 1, if I chose 'abc' in one menu, and 'def' in the other, i
would get something like this in sheet 1:


aposatsk's Profile:
View this thread:


I have a spreadsheet which I would like to hide columns based on two sets of criteria contained in drop down menus.

I have two drop down menus, one has month (Jan-Dec) in cell A2 and one has year (12, 13, 14) in cell B2.

The months run in columns D4 to BV4 with a space column in between each month e.g. column D = Jan-12, column E = splitter, column F = Feb-12.

I would like users to be able to filter to show only a specific month and year based on choices made in the two drop down menus.

I have attempted to find an answer in forums but as I am new to VBA I am struggling and I am not sure if it works using drop down menus? Any help would be greatly appreciated!

Thank you!

Hi, is there a way to use code to display the value selected from the
drop down list directly on a specific cell? I originally used the
vlookup function and it worked well. However, I have added the
following code to my two related drop down and the vlookup function for
matching the value of drop down 2 is no longer working. Could anyone
please help me out? If VB code is required, where should I put it?
Thanks in advance.

Option Explicit
Sub ddMstr()

Dim dd As DropDown
Dim dd1 As DropDown

Set dd = ActiveSheet.DropDowns(Application.Caller)

With dd
If .ListIndex > 0 Then
Set dd1 = ActiveSheet.DropDowns("drop down 2")
dd1.ListFillRange = Worksheets("sheet2").Range("list" _
& dd.ListIndex).Address(external:=True)
dd1.ListIndex = 0
End If
End With
End Sub

Sub ddSub()
Dim dd As DropDown
Set dd = ActiveSheet.DropDowns(Application.Caller)

With dd
If .ListIndex > 0 Then
End If
End With
End Sub


I searched the forum but couldn't find anything that was like the problem I have. I have two worksheets, one that I'm using as a data base of which some fields have only a cretin amount of options to choose from so I deiced to use a drop down menu. The other worksheet I use to hold the menus and options associated to each menu, and link it back to the drop down menus by defining a name.

The problem I'm having is that some menus well have options added to them in the future and I want to be able to add them to the menus by just entering a new option to in the cell below. Each menu is associated to a different column and has a different amount options which are entered in the rows. So I just highlighted from the beginning to 65536 and figured this would work. Now when I make the validation drop down menu all the menus that have a lower amount of cells then the longest one has blanks below it so it will be the same length as the longest. I'm wondering how to fix this?

I was trying to find a formula that would identify the last cell in a column with data in it, I’m trying to stay away from macros partially because I’m not to use to them and that many people in my office will be using this so if it gets messed up it won’t be fixed.

Thank you for you time,
Rocky Strong

I am undertaking a project in sales order processing and am using drop down menus to allow the order entry clerks to chose the customer and location, is it possible to use the resultant cells in a lookup table, so that if, for example, the drop down returned Joe Bloggs and Aberystwyth it would then look at another table and complete four fields; Pallet type, pallet height, multiple layers, multiple products and so on?


Uncle H

Evening all,

I have a question regarding the use of drop down menus and having formulas as well as cell values change based on the selection from the menu. I've attached an example sheet with separate tables each showing what the results i want when a certain value from the drop down menu is selected. I want to be able to apply the specific settings to one table based on that one value from the drop down menu.

Thanks in advance!

Hello there,

I am working on a template for a form. This form could contain between 1 and 40 line items. In order to reduce the number of irrelevant cells shown I want to hide blank cells which arent required. I have a drop down box where you can select the number of line items 1-40 in (cell F22) and want the worksheet to hide rows as follows accordingly;

Value from Drop-Down Hide Rows
1 27-65 2 28-65 3 29-65 4 30-65 5 31-65 6 32-65 7 33-65 8 34-65 9 35-65 10 36-65 11 37-65 12 38-65 13 39-65 14 40-65 15 41-65 16 42-65 17 43-65 18 44-65 19 45-65 20 46-65 21 47-65 22 48-65 23 49-65 24 50-65 25 51-65 26 52-65 27 53-65 28 54-65 29 55-65 30 56-65 31 57-65 32 58-65 33 59-65 34 60-65 35 61-65 36 62-65 37 63-65 38 64-65 39 65
I have seached everywhere with no joy - can anyone help with code???


I know it's possible... how do I make drop down menus in excel? I have the forms toolbar open but I'm not sure what I'm doing... I understand how to work forms in Word. Are they at all the same?

Thanks in advance?