Free Microsoft Excel 2013 Quick Reference

Assign values to dropdown menu selections


I have a dropdown menu in cell A9 with 5 selections in it. I want 4 of the selections to have cells automatically populate when they are selected (I have embedded IF statements into the cells that I want to change). However, I want to have one of the selections in the dropdown menu be an "open" selection, where I can enter in my own values in the same cells that would be populated if one of the other 4 selections were picked.

Does anybody know how I can tell the IF statement to allow me to input my own data when the "open" selection is chosen, while still re-populating the fields when the other 4 selections are picked?

Unfortunately, I can't upload an example because there is private information that I can't share. If you want me to clarify anything, please let me know!


Post your answer or comment

comments powered by Disqus
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!!!

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).


I have 2 drop down lists, 1 is inconsequential, but for the other I need values to be known in other cells so that when I type in numbers it will do the math and populate. For instance. We will use #### to show an empty cell.

#### #### #### #### ####

I choose from a dropdown:

Front Door #### #### #### ####

I would like the time it takes to pain the Front Door (.81hr) and the amount of Paint(.46gal) to populate the third and fourth cells.

The second cell is how many doors there are to paint. When I type a number into that I would like the fifth panel to do the multiplication of 3rdcell*4thcell*1.07375.

I have gotten as far as creating the dropdowns that I need, but I am currently stuck.

Assign values to ratio buttons

I have created a UserForm in Excel, to gather data for a questionnaire.

For each question, the user must select an answer from a radio button (Yes, No and N/A)

When I add the record (answers) to the sheet1, the results are shown as all being false.

I want to show either ‘Yes’ or ‘No’ or ‘N/A’

How do I do this????

Private Sub CommandButton2_Click():
Dim LastRow As Object
Set LastRow = Sheet1.Range("a65536").End(xlUp)
LastRow.Offset(1, 1).Value = UserForm1.OptionButton1.Value 
LastRow.Offset(1, 1).Value = UserForm1.OptionButton2.Value
LastRow.Offset(1, 1).Value = UserForm1.OptionButton3.Value
End Sub

Thanks in advance

I'm trying to figure out a way to automaticcaly assign values to a cell (B3)
based on values typed on cell A3. Example:
A3 values (typed) = 10%, 30%, 50%, 70%, 100%
B3 values (automatically assigned) = min, low, medium, high, max

Tks for the help.


I wanted to assign constants to an array at once.

Dim KK(1 to 10) as double

KK = ( 1, 2, 3, 4, 5, 6, 7,8,9,0)
Doesnt work...

How can I assign values to the array at once without assigning one by one.
KK(1) = 1
KK(2) = 2
.... etc


I have a set (approx. 30) of words that I have grouped into 4 categors and
can easily assign values to them (ie. 0, 1, 2, 3). I would like excel to
recognize the word, and automatically assign the value to it. How do I do
this? Please help.
Thank you,

Is there some elegant way to assign values to array, for ex:
dim A(3) as integer {1,2,4}

instead of
dim A(3) as integer


Hello. I am creating an Excel workbook for scheduling and billing tutored students. One sheet contains the schedule, another the students and their rate, and the last the teachers and their rate. The rates won't be visible in the schedule sheet, only the student and teacher names. Therefore, I need to assign the value of the rate to the name as though the name were a variable. If the values were updated they would need to update on the schedule sheet too, where the final calculating happens. This is complicated by the fact that students and teachers can be moved to any time slot.

I looked at "Linked Objects" but saw nothing about assigning values to a variable name. If it is possible, how would I do it?


I'm looking for a way to assign values to letters so I can average columns but retain the letters in the column. I've been able to successfully overwrite my letters with appropriate values and subsequently avg, but that's not what I'm looking to do.

I've attached a sample of what I'm trying to accomplish. I'm looking to keep a running average of each column on the right where D=1, P=2, and M=3.

Sidenote: File isn't as diabolical as it may seem Trying to put something together for employees to keep their own running tab of accomplishments so they are actually prepared for reviews.

Edit: Fixed attachment.

I want to be able to create terms and assign values to term that will be used in all calculations on the page.

I know I can go to Insert>Name>Define and in the "Names in workbook" box type: the term, such as "labor1", then assign a value in the "Refers to" box. This works if I use the computation in the top field, for example SUM(3*labor1).

However, what I want to do is have a spreadsheet that says
"3" in column A, "labor1" in column B and the product in column C.

How can I accomplish this?

I have 2 questions

Hi, I have a variable name Counting.
I want to have a value from the function "count(a2:a500)." But I want to avoid putting a real value in an actual cell.

How can I write a syntax in VBA to assign value to the variable?
For example,
Dim Counting as Integer
Counting = Count(A2:A500)
Second Question.
After Counting has a value already.
How can I show the value of count in a window that is not a MsgBox.
I want to countdown how many times this Macro will finish, but all I know is MsgBox which you have to press OK everytime it comes up.

I am using a macro to assign a value to a pivot field. We have a drop-down
menu that the user selects a value from and through a vlookup, the value that
is selected from the menu is then selected in a specific pivot field. Our
problem is that when the macro runs, instead of just selecting the selected
value from the list contained in the pivot field, it actually changes the
current value to the new "user selected" value!

How do I go about telling excel to select the value from the list instead of
overwriting the current selection?

Here is what the macro looks like as of right now...

Sub ClassDescriptionSync()

With ActiveSheet.PivotTables("ClassDescTbl1")
.ManualUpdate = True
.PivotFields("Class Description").CurrentPage =
.ManualUpdate = False
End With

End Sub


I have a look up table, which I use to populate an activeX combobox, which is embedded in a worksheet. On another worksheet I have a lookup table. The table in the seperate work sheet "tables" is similar to:

--------A--------- B------- C
1--- Title 1------ 1------ 100
2--- Title 2 ------2 -------70
3--- Title 3 ------6 -------60
4--- Title 4 ------2 -------20

The code that fetches the items into the ComboBox menu is

Private Sub ComboBox1_Click() 
    Sheet7.ComboBox3.ListFillRange = ThisWorkbook.Worksheets("Tables").Range("A1:A4").Address(external:=True) 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This populates the ComboBox with 4 items, Title 1, Title 2, Title 3, Title 4. What I would like to do is have values in columns B and C populate two activex text boxes depending on the item choosen. So if item 1, "Title 1", was choosen, then values B1 and C1 would populate textbox 1 and textbox 2 respectively. I cannot use 4 if statments, because the real table is 163 rows, which would require 163 if statments.

If item 1 is choosen, then "Title 1" appears in the Value field as well as the text field in the comboxBox properties. Is there a way of have the value field have the corresponding choosen row?

Would really appreciate some help on this, thank you.

Hi. for some reason my code below will not work, when i select the drop down list i display a pop up Calendar, when i want to assign a date in my pop up Calendar to a combo box with the follwoing line
cboStartDate.Value = Calendar.Value i get you cannot assign a value to this object... any help would be great. thanks

Private Sub cboStartDate_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single) 
    Calendar.Visible = True 
     ' Set to today if OrderDate has no value.
    Calendar.Value = IIf(IsNull(cboStartDate), Date, Calendar.Value) 
End Sub 
Private Sub Calendar_Click() 
     ' Set OrderDate to the selected date and hide the calendar.
    cboStartDate.Value = Calendar.Value 
    Calendar.Visible = False 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I'm doing somthing basically wrong here but i'm not sure what, everything seems to work as planned except in each case where a value is assigned the value isn't actually assigned. any ideas?
The basic objective is to convert a list of numbers (1 to 3 digits) to the format "000-" i.e. all three digits with a hyphen at the end.

        Dim rngCclist As Range, i As Integer, iCCLength As Integer
        Set rngCclist = Range("AB2:" & "AB" & RowCount)
        For i = 1 To RowCount
            iCCLength = Len(rngCclist.Value(1, i))
            Select Case iCCLength
                Case 1
                    rngCclist.Value(1, i) = "00" & rngCclist.Value(1, i) & "-"
                Case 2
                    rngCclist.Value(1, i) = "0" & rngCclist.Value(1, i) & "-"
                Case 3
                    rngCclist.Value(1, i) = rngCclist.Value(1, i) & "-"
            End Select
        Next i
Or if you have any suggestions for a better or more effecient way to acheive this that would be good. I don't want to just use formatting because I need the actual values to change.

I have designed a form with check boxes as well as cells that will contain numeric data. My goal is to have a cell that calculates all of the check boxes and cells to a total.

Where I'm stumped is in assigning numeric values to a check box. For example:

1. Client will click the check box next to the product to order.
2. Client may click more than one (out of 24 boxes).
3. Form will view if the box has been clicked, it will assign a dollar amount to that check box. If the box has not been selected, then value is 0.
4. Total box will add up the value of all boxes selected as well as add the numbers entered in the free form cells.

Is this possible?

And currently, I'm having trouble just making the check box understand the difference between true and false.

Oh, and I have to have this done by Friday, 10/30/09.
Thank you in advance for your help.

In Excel I have a list of numbers in a column where some of the values repeat. How can I assign unique values to each number based on the number of times the value occurs in a list??

For example - I start with this:

Column A

And want the result to be as follows:
Column B (or wherever)
15.1 (1st occurence of 15)
21.1 (1st and only occurence of 21)
15.2 (2nd occurence of 15)
15.3 (3rd occurence of 15)

So, I guess I'm looking for a formula/solution that would allow me to assign a ".1" to the first occurence of a value, ".2" to the second occurence of the value, ".3" for the third and so on.

Any help/suggestions would be greatly, greatly appreciated.

how can we assign a value to the cell(list) with some type text from VBA.

Good morning,

I have attached a sample workbook of my data to make this easier.

What I need to do is assign a value "Load# x" in column "I" to each row. Where x is 1 to start with, and then increments up +1 each time the total weight of the current Load# x has reached a maximum of 40,000lbs or there are no more shipments that can fit into the same Load# that will keep the weight at 40,000lbs or less.

The other constraints for assigning orders to a load are as follows:
Orders must be assigned by priority level. Priority level 3's must be first, then 16's, then 17's, then 0's are last or no priority.Orders with the same Ref# (Column "H") must not be assigned to different Load#'s.Total weight of a load cannot exceed 40,000lbsWhat I figure is the easiest logically would be to do the following:

Sort the data by Ref# then by Priority #, then by Weight.

The loop would start with Priority# 3. Assign "LOAD#1" to the last column. Then it will search through the remaining data for any shipments where the same REF# is present. If it is found, it will add "LOAD#1" to this row as well. Once all the same REf#'s have been found for the initial shipment, it will then start searching for the next priority 3 shipment and so on, until it reaches the weight max of 40,000lbs. Once the weight max is reached, it will continue with the logic above by assigning shipments (rows) with a "LOAD# x " value with an increasing x value.

I understand the logic required, I am just so unfamiliar with loops that I am being bogged down by this.

Thanks in advance!

Here's the scenario: I have a table like this, with the date extending for 15 more years.

Events | Jan-10 | Feb-10 | Mar-10
1st Unit
2nd Unit
3rd Unit
4th Unit
5th Unit
6th Unit

what I have to do is automatically assign the correct numbers into the correct cells. For example, if the 1st unit is sold on Dec 2011, I have to find that cell, and insert the price that unit is sold. Then if this unit is repaired 5 years later, I have to assign that cost into the corrected in, which is Dec 2016.

I will have to do this with 8 different numbers for each role. I'm wondering whether there is a way that doesn't require writing 8 "if" statements. My boss said something about "vlookup", "match", and "index" functions. But none of those actually assign any values to cells.


I have a 2 column listbox which populates itself when the form is initialized. This form can either be opened to add a new record or edit an existing record.

When I edit a record I load the form with the data but although the listbox showing the correct text, when I come to save the data there are no values in the either column(0) or column(1).

This is how I assign the value on load

Frm_promo_details.SKUID.Text = rs.fields(9)

Obviously this isn't the way to do it, so how should I assign the value to a prepopulated listbox when I open the form?


Can excel assign a value to a text input? In order to calculate attendance of an event? For example I would like to be able to copy and paste a list of names on one page and then on a front page have excel calculate attendance percentage. Any help is greatly appreciated

my objective is to create VBA Function equivalent of this formula:


as you can see contains a 2x17 array

VBA code so far:

Function ChartNum(Atlas As String, ra As Date, dec As Variant) As String 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The next step is to assign the values of the 2x17 array

I see from VBA help the function to assign a 1 dimensional array is

U1 = Array(-90, -84, -72, -61, -50, -39, -28, -17, -6, 6, 17, 28, 39, 50, 61, 72, 84) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
the problem is I need to assign values to all the dimensions, with 'something' like this hypothetical code:

U1(1, x) = Array(-90, -84, -72, -61, -50, -39, -28, -17, -6, 6, 17, 28, 39, 50, 61, 72, 84) 
U1(2, x) = Array(472,460,440,416,386,350,305,260,215,170,125,89,59,35,15,3,1) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
it seems like it should be possible to assign values to the "n-th" row or dimension with a statement, since it can be done at the 1 dim level without a loop.
how do I do this WITHOUT using a loop? (preferred)
how do I do this WITH a loop?

Thanks in advance.

No luck finding an answer? You could always try Google.