Assign Values To Names In A Dropdown List?

Mar 8, 2005

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

View 5 Replies


ADVERTISEMENT

How To Assign Value To Each Name In Dropdown List

Mar 25, 2014

I have recently found out how to create a dropdown list (easy). Now I am wondering if it is possible to create a dropdown list that has a value assigned to each name in the list?

For example: I have the following list;

Australian states:
1) QLD
2) ACT
3) WA
4) NT
5) NSW
6) TAS
7) VIC

When a person chooses their "state" I want there to be a corresponding value attached to it. These values are:

1) QLD - 0.82
2) ACT - 0.87
3) WA - 0.78
4) NT - 0.69
5) NSW - 0.87
6) TAS - 0.20
7) VIC - 1.17

Is this possible?

View 4 Replies View Related

Create Dropdown That Will List All Names Starting With Initial Letter Of Names?

Jan 13, 2014

I have a long customer listing, names 5 to 36 characters and several with their location in the name as well as a few common duplicate names. I am trying to produce sheet where the customers name once selected opens in the customers spreadsheet and data can be added/amended for sales etc.The lists are not in alphabetical order as when created a customer number is automatically allocated. My aim is just to type in the first letter of the name and the dropdown appears the customer is selected and their card appears. I have tried data validation, lookup, vlookup, Dropdown and Match/find. they only return the first record found and no sign of any others. Find returned all instances of the letter appearing in every name.

View 9 Replies View Related

How To Assign Macro To Item On Dropdown List

May 30, 2014

I created a drop down list (of various Input Groups) and I need to show the selected group's responsibilities on a separate sheet; is there an easy way to do this?

View 2 Replies View Related

Assign Names With Specific Numerical Values

Feb 27, 2008

My company is soon to be rolling out a new payment system, and I'd like to be able to track commission for everything I sell. Briefly, we get paid via a percentage of the company's gross profit, and the way in which the company gets paid is tiered. (the cellphone business)

So, If a customer activates a plan thats below $39.99, we get paid X amount. If it's below $59.99 we get paid Y amount, and so on and so forth, there are multiple different tiers.

What I want to do is set up a list via data validation so that I can pick what plan they have (via the name of the plan) and have it return a numerical value (ie $39.99) and also have the ability to pick text messaging, etc as options so that the workbook will add the monthly rates (39.99 + 14.95) and then have it return value for the right priceplan tier.

If this is confusing, I apologize for not being more clear, but attached is an example. The top one is what I'm actually trying to code, but the bottom is completely filled in, so you can get a more clear sense of what I'm trying to do. Honestly I'm not even sure if it can be done.

View 2 Replies View Related

Assign Group Names Based On Cell Values

Feb 19, 2010

I have two tables below. First table contains the list of domain names (given just for example, actual list may contain several more such Domain names) with the group names assigned to them. Second table shows the criteria for assigning them the group names:

Sheet2ABCDEFG1Table 1Table 22Domain NameGroup NameDomain NameGroup NameDomain NameGroup Name3NCG-W3K-SAT14-0DOSSNCG-W3K-EEC-SUN16-3EECEXCEEC4SVL-WIN-SAT08-0LABSNCG-W3K-SUN16-0DOSSDCDC5SVL-WIN-SAT06-0LABSNCG-W3K-TUE01-0DOSSNCGDOSS6NCG-W3K-MON04-0DOSSNCG-WIN-EXC-SUN21-0EECDMZETIS7NCG-W3K-EXC-MON04-3EECNCG-WIN-EXC-SAT16-0EECSVLLABS8NCG-W3K-EXC-MON03-0EECNCG-W3K-X64-DC-SAT14-0DC9NCG-W3K-EXC-TUE01-0EECDMZ-NCG-W3K-X64-SUN12-0ETIS10NCG-W3K-EXC-SUN15-0EECDMZ-NCG-W2K-SUN12-0ETIS11NCG-W3K-X64-EXC-SUN16-0EECNCG-W2K-SUN20-0DOSS12DMZ-NCG-W3K-SUN11-0ETISNCG-W3K-MON03-3DOSS13NCG-W2K-DC-SUN11-3DCSVL-DMZ-WIN-SUN12-3LABS14NCG-W3K-X64-DC-SUN10-3DCSVL-DMZ-WIN-SAT10-0LABS15SVL-DMZ-W2K-SUN12-3LABSDMZ-NCG-W3K-SUN12-0ETIS16NCG-W3K-EXC2-MON03-3EECDMZ-NCG-W3K-SUN10-0ETIS17NCG-W2K-SAT17-0DOSSNCG-W8K-X64-DC-SUN10-4DC18NCG-W3K-X64-SUN10-0DOSSNCG-W3K-TUE01-3DOSS19NCG-W3K-TUE00-0DOSSNCG-W3K-DC-TUE02-0DC20NCG-W3K-X64-SAT15-0DOSSSVL-WIN-SAT11-0LABS21NCG-W3K-WED00-0DOSSSVL-WIN-SAT07-0LABS22NCG-W8K-X64-SUN19-0DOSSNCG-W2K-SUN17-0DOSS23NCG-W3K-EXC-SUN20-0EECNCG-W3K-SUN20-0DOSS24NCG-W3K-ULZ-SUN14-0DOSSNCG-W3K-TUE22-0DOSS25NCG-W3K-TUE03-0DOSSSVL-WIN-SAT07-3LABS26NCG-W3K-ULZ-TUE00-0DOSSNCG-W3K-MON05-3DOSS27NCG-W3K-SUN19-0DOSSNCG-W8K-SUN18-3DOSS28NCG-W3K-EEC-SUN19-0EECNCG-W8K-SUN22-0DOSS29NCG-W3K-EEC-SUN20-3EECNCG-W8K-X64-DC-SUN11-0DC30NCG-W3K-EEC-THU00-0EECNCG-W3K-SUN17-0DOSSExcel 2007

My concern is to automate this process using a VBA code. How can I search for the keyword given in Table 2 in the first column of Table 1 and assign the group names as it is assigned currently, considering the below conditions and criteria.

1) The correct group name should be assigned to the Domain name containing the keyword given as per criteria in Table 2

2) It is possible that the domain name will include more than one keywords given in Table 2. For example Cell number A7 contains both keywords NCG and EXC, in such cases the preferance should be given to EXC and we will assign the group name as EEC. And in Cell number A12 also contains both NCG and DMZ, again the preferance should be given to DMZ and we have to assign group name as ETIS.

That means we will give second preference to keyword NCG. If domain name contains none of the keywords except NCG as per given criteria then only we will assign grup name DOSS.

View 9 Replies View Related

Dropdown List With 9 Golf Course Names

Dec 30, 2013

I am building a golf spread sheet with page 1 being where I will input scores, also on this page I have a drop down list with the 9 golf course names. What I have done is I have added 9 sheets with the names of each golf course, on this sheet I have added the scorecard. So what i am wanting to do is, on sheet 1 use a drop list with the name of the course, then choose what tees we are using and let it populate on sheet1 hole 1 thru 18, with the yardage and par for the hole.

View 4 Replies View Related

Dropdown List With Customer Names - Linking Them To Other Fields

Feb 16, 2014

I've had a search on here, but can't quite find what i'm looking for. I have a cover sheet with a drop down list which has customers in it. under it I have the customers address, phone etc. What I am trying to do is when I click on a particular customer, I want the address and phone numbers to all fill in automatically. I have tried a few things, but to no avail.

View 12 Replies View Related

Remove Names Without Values From A List Of Names And Values

Mar 1, 2013

I have the list below and would like to create a new list which contains only names with corresponding values and lists them.

+ A B
1 James 3
2 Derek
3 Brett 6
4 Allan
5 Jess 7
6 Sam 10
7 frank 10

The solution should look like:

+ A B
1 James 3
2 Brett 6
3 Jess 7
4 Sam 10
5 frank 10

View 2 Replies View Related

List Values From Multiple Columns In A Dropdown List

Dec 17, 2012

how to list values from multiple columns in a dropdown list based on lookup value of 1st column as below.

This is how the table looks like.

Product MOLD1 MOLD2 MOLD3
4" AB1 AB2 AB3
6" ZA2 zd4 -

This is how the dropdown list should look like for Product 4"

ab1
ab2
ab3

View 6 Replies View Related

Dropdown List With Multiple Values?

Sep 17, 2013

I am trying to create an excel spreadsheet that calculates nutrition based on a dropdown menu. The values would change depending on the formula selected. How to I pull the data from sheet 1 and bring it in sheet 2 in order to calculate protein, carbohydrates and fat content. I have attached a copy of the excel spread sheet.

View 4 Replies View Related

Getting Values For Dropdown List Items

Mar 25, 2008

I created a drop down list for products my company sells. Now I am wondering how can I get Excel to display the cost of the item in another column so I do not have to type in the cost each time I bring that product up.

I have searched google and this site but of course I can't describe my search well enough to find what im looking for.

I'm sure this has been explained I just can't find the answer to my problem.

View 9 Replies View Related

Display List Of Values From Dropdown Window?

Jul 15, 2013

I need to be able to display a list of values from a drop down window. When the value in the drop down window is selected I need to have the data populated in the rows to the right of the drop down window. I have created the Drop Down using data validation in B12, I need C12 & D12 to auto fill with the info listed in H2 & I1 according to the item picked in the drop down.

I have tried to follow an example listed on the website but i keep getting a formula error.

View 1 Replies View Related

Controlling Cells Values From Dropdown List

Jan 28, 2010

I want to control 2 cells values from a dropdown list (linked to another worksheet). How is it possible?

I can do it with embedded ifs as long as the number of values is not too big, but I want to get more extended.

View 7 Replies View Related

Dropdown List Depending On Previous Values?

Jan 3, 2013

see attached document - this is a stock record sheet. what i want it to do is when Bed is seleceted in column B - i want colum C to show the BedSize list which is in the lists tab. and the same with Chair i want it show ChairSize

View 14 Replies View Related

Returning Multiple Values As Dropdown List?

Apr 24, 2013

Actually I plan to create an eRequisition Form for our branch sites. My focus would be the consumable items of Lexmark printers. I want to create cascading drop-down lists based on data table (worksheet : All) as per attached. From lots of examples I've seen in this or other similar forums, I notice that in order to allow this cascading to work is by creating column with header name identical with the input in the 1st List. But I'm thinking, if I need to do that, then there gonna be hundreds or maybe thousands columns to create as the number of branches are expanding, so I think tht should not be practical.

I found examples that shows how lookup 1 value can return multiple values but I totally cannot think of a way to allow those multiple values to be returned as dropdown list rather than displaying all in rows of the worksheet.

What I need is that, in 1st list, upon selecting the branch, it'll populate all items related to the branch and when selecting the item, it'll populate brand/model list for the particular item only. The input in the dropdown list should not have duplicates. Been trying using pivot to do this with macro that enables auto refresh once any changes made in the table.

create this cascading drop-down lists just based on the table as per attached? I really want to avoid having to create extra tables to allow this cascading to work.

[URL]

View 13 Replies View Related

Enter Value From Dropdown List And Have Values Update

Sep 26, 2007

im creating a data sheet of a slide rule (pull the rule out and all the numbers change, you know the sort).

I want to be able to select a value in a dropdown list, and the others (already programmed in) to appear next to a diagram of a screw.

View 11 Replies View Related

Dropdown List Range Based On Two Different Cell Values

Aug 2, 2012

I'm new to excel and was trying to make a workbook that will have a drop down list populate based on a user input in two separate cells. Basically, i have columns: name, pages, and books. I would like to have the drop down list match data by first finding the amount of books read, then finding the amount of pages read. Then any name that meets the criteria will be available in the drop down list. How can I do this?

View 1 Replies View Related

Validation Rule Dropdown List Without Duplicate Values

Feb 9, 2012

How to get rid of duplicate values in validation dropdown menu. I am using a range for the list that has a lot of repetitive values. I want to have only unique ones in the list.

View 9 Replies View Related

Copy And Paste Values Without Including Dropdown List

Jul 15, 2014

The source data is collected using drop-down menus. In moving the data from one workbook to another, the drop-down lists are carried over into the destination cells. I have used both: paste special "values" and paste special "valuesandnumber formats". The code in the else statement produces the same result as the 2 step process above it.

View 1 Replies View Related

Creating Payroll Spreadsheet With Dropdown List Linking To Values

Dec 3, 2013

I am trying to attempt to create a payroll spreadsheet with certain aspects and with multiple worksheets. so what im trying to accomplish is this

1/ on my main worksheet would have the payroll template there would be 2 columns (1) would be "routes" (2) would be the dollar value of that route.

2/ i want to be able to pick from a drop down list in colume (1) which will have various routes such as A,
B, C, D, E etc

3/ once i have selected a route in colum (1) i want to have the dollar value that is associated to that route to show up in column (2)

ex/ route A worth $1
route B worth $2

create a dropdown list but thats about it, i have not been able to link any values together.

View 11 Replies View Related

Excel 2010 :: Resetting Cell Values (Dropdown List)

Jun 23, 2014

I'm using Excel 2010. I have developed a calculator (attached) to make life easier for my colleagues. It works perfectly fine. But one thing keeps bugging me.

In Cell C3 - You have the option to choose "Daily" or "Weekly" (Drop Down). This selection effects your selection for Cell C6.

If you choose Daily in Cell C3 then your options for Cell C6 are restricted to select/enter an amount between £0.00 and £6.00.

If you choose Weekly in Cell C3 then your options for Cell C6 are restricted to select/enter either £0.00 or £12.00.

This works perfect.

However, if I select "Weekly" and then select " £12.00", all my results are perfect which is exactly what I want BUT if I then go back up to C3 and change "Weekly" to "Daily", the £12.00 in C6 remains (though the data validation for "Daily" is restricted to £0.00- £6.00) and all the results are consequently wrong until C6 is changed.

The obvious thing is to delete the £12.00 from Cell C6 or introduce a "clear" button BUT I don't want my colleagues to do this as it leaves the possibility of error and since we are dealing with money, I can't have it.

So what I would love and what would complete this calculator and deem it ready is if every time Cell C3 is selected (i.e. from the drop down list you choose Weekly or Daily) it as a result resets Cell C6 to £0.00.

Calculator 23-06-14.xlsx

View 6 Replies View Related

List Values In Pivot Table Page Field Dropdown

Feb 27, 2007

I want to print one sheet for each of the values in a page field drop-down. I can do this by supplying the values individually (code below) but would rather determine the list of values dynamically. Can this be done?

Private Sub pbPrintAll_Click()
Dim cix As Integer
Dim Ctrct As String
cix = 3
While (Sheets("Database"). Range("B" + Trim(Str(cix))).Value <> "")
' Get value from database sheet
Ctrct = Sheets("Database").Range("B" + Trim(Str(cix))).Value
' Set CurrentPage value
Sheets("Customer P&L Pivot1"). PivotTables(1).PivotFields("Cust 1A_Name").CurrentPage = Ctrct
' Print formatted sheet
Sheets("Customer P&L").PrintOut
cix = cix + 1
Wend
' Reset Current Page value
Sheets("Customer P&L Pivot1").PivotTables(1).PivotFields("Cust 1A_Name").CurrentPage = "(All)"
MsgBox "Prints sent to printer."
End Sub

View 4 Replies View Related

Dropdown List Always Shows Unique Values From Column Based On Lookup Value

Jul 22, 2014

I have a worksheet (say, Sheet1) that I am going to manually import a large data set into on a weekly basis for reporting purposes. One of the columns from this data dump will have the header "Design Id" in the first row, but it may not be in the same column every time. I am trying to figure out how to create dropdown lists on a separate worksheet (say, Sheet2) in the same workbook where the data source always looks up the column containing the header "Design Id" from Sheet1 and then returns only the unique values from that column as options in the list.

View 1 Replies View Related

List Files & Cell Values With Specific Sheet Names From ListBox

Dec 4, 2008

I have this script (below, Krishnakumar orignally provided this script).

I'm trying to edit it to add cell values from cells C16 and E16 along with the worksheet name it displays in listbox2. So lets say worksheet "rollover" is the sheet being displayed in the listbox2.

I would like for it to look something like this:

rollover TT:'data from cell C16' TA:'data from cell E16'

Public FilePath As String
Public dic As Object
Public oWB As String
Public oWS As String
Public aWS As Worksheet

Private Sub CommandButton1_Click()
Dim i As Long, wb As Workbook, n As Long
With Me.ListBox2
For i = 0 To .ListCount - 1
If .Selected(i) = True Then
oWS = .list(i)
Set wb = Workbooks.Open(FilePath & oWB, UpdateLinks:=0)
wb.Sheets(oWS).Activate
Exit For
End If
Next
End With
End Sub

View 7 Replies View Related

Create A List Of Unique Names From A List Of Multiple Names

Oct 21, 2009

I have a database output file where one of the columns contains managers names, often more than once. I want to apply an autofilter on manager name and then copy the result to another sheet or sheets. My criteria for the autofilter is a variable pointing to a list of names that at present I maintain by hand; a for-each-next loop then cycles through the names.

What I would like to do, before running the autofilter code, is to create the list of names via code. This would then automatically pickup names that are missing.

The code I have so far is below:

Public Sub find_managers()
Dim managers1 As Range
Dim names1 As Range
Dim n1 As Variant
Dim n2 As Variant

In my mind it should check the names in the unique list against the imported list and add any missing names.

View 9 Replies View Related

Excel 2007 :: Lock / Unlock Cells Based On Values Selected In Dropdown List

May 15, 2013

I am looking to write a code to lock/unlock cells based on values selected using a drop down list (see attached Excel) For example if Netherlands is chosen as the Country (Column A), then except for the columns Amsterdam (column B) and Eindhoven (column C), all other columns must be locked. Similarly, if India is chosen as the country from the drop down list, then only the columns New Delhi and Mumbai must be editable for the user, the rest of the columns remain locked.

I tried tweaking some of the lock/unlock codes around, but got lost especially with getting to run the macro - still trying to come to terms with running a "Private Sub Worksheet_SelectionChange" function in the sheet from "Module"!

View 1 Replies View Related

Can Use Variable Names To Identify / Assign To Other Variables Or Arrays?

Apr 10, 2014

I have set up a number of arrays e.g. DataSetA (1 to 100), DataSetB (1 to 100) etc.

Is it possible to use a string variable (e.g. DataIndentifier) to identify and assign values to certain arrays.

For example, under certain conditions

DataIdentifier would be set to "DataSetA" and under other conditions
DataIdentifier would be set to "DataSetB".

Having done this, I want to assign values such as DataIdentifier(1) = 3.4 or DataIdentifier (10) = 6.2 etc.

This obviously won't work because DataIdentifier is defined as a string not an array. How do I enable the string DataIdentifier to be used as an identifier for the array I want to assign values to? Therefore if DataIdentifier is set to "DataSetA" then effectively I want something like "DataIdentifier(1) = 3.4" to assign 3.4 to the array DataSetA(1).

View 5 Replies View Related

Assign Names Dynamically Based On Cell Content

Jul 10, 2009

I do have a data set from A1:C8 as follows:-
Brand Name(A1)Sales Value(B1)Margin(C1)Levi's100002000Arrow150002500Allen Solly120001300Hanes80001000John Miller250004500Disney280003200Ray Ban180002100

The above brands are categorized as follows:-
Brand NameProposed CategoryLevi'sConsignmentArrowBrandAllen SollyBrandHanesBrandJohn MillerPrivate LabelDisneyBrandRay BanConcessionaire

I would like to put formula from D1:D8 in such a way so that the Proposed categories are extracted automatically through formulae. This will help in getting the contribution calculated from different categories.
Brand Name(A1)Sales Value(B1)Margin(C1)Category(Desired Result)(D1)Levi's100002000ConsignmentArrow150002500BrandAllen Solly120001300BrandHanes80001000BrandJohn Miller250004500Private LabelDisney280003200BrandRay Ban180002100Concessionaire

View 9 Replies View Related

Excel 2010 :: Assign Macro To Value In Validation Dropdown Menu?

Feb 28, 2013

I have a validation drop down menu with the 12 months in it on Sheet 1. User picks the current month from the drop down list. I have 12 macros in the workbook, one for each month. Based on what month the user selects from the drop down, I would like to run a specific macro. The macro hides specific columns on sheet 2 of the workbook.

for example...user picks May from drop down which triggers macro MAY to run. If user picks Jun next month, macro JUN will run.

fyi...I'm using Excel 2010.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved