i mtrying to get a validation list change depending on what is chosen in another list. I have attached an example, Yellow box is my validation and weather List 1 or List 2 in chosen I want the red box to be a choose of the list attached to those options. I've tried to put an If in there but I’m al a loss.
View 3 Replies
I see that IF statements can only be nested 7 times. How do I create a cell that checks to see what item was selected in the dropdown box of 20 different choices and choose a particular cell range depending on what is chosen?
View 9 Replies View RelatedSee attached spreadsheet, in sheet 1, yellow columns are for restricted cells and Sheet 2 contains the lists for such columns' validation range. Sub-Strategy is dependent on the choice made from Main-Strategy, e.g. if Main-startegy = EquityHedge, then the choices for sub-strategy can only choose Long Biased, Low Beta & opportunistic. But I also have a "Other" category under Main Strategy, Sheet 2 cell G1, if this is chosen then I would like to remove the data validation in the sub-strategy column, (i.e. u can input anything for sub-strategy) how can I do this?
View 2 Replies View RelatedI am trying to create a fairly simple spreadsheet with about 8 columns and about 400 rows. One of the columns features a drop-down list with about 8 or 9 different options. Dependant on which option is selected, i would like the entire row to change colour with that option.
For example:
FAILED - whole row changes red
SUCCESSFUL - row has no fill
Tested - row changes to orange
etc.
Is this possible within Excel 2003?
I have a drop down list in a column where i want the user to select one of the options which is an action to be taken. The actions would be dependant on the what the persons performance rating was, so for example, if the person has Exceeded their performance targets, the only 2 applicable actions would be High Potential and Emerging Talent. Please see attached file for the actions related to the peformance rating.
I want the validation list of actions to change accordingly depnding on what the persons performance rating is. So in the example attached, i want to look up the rating in column A and have the options appear according in the same row in column B.
Example:
Sheet: Users
Column A has "Yes and No"
Column B has "Names"
ex.
A B
Yes Peter Johnson
No Patrick Andersen
Yes John Smith
Now i would like to create a list in sheet [Employee] where one the users with Yes in column A is shown.
In this ex.
Peter Johnson
John Smith
No need to show yes or no.
I created validation list using Validation option in Data menu. Now I would like to change font for that list.
View 3 Replies View RelatedI have a cell that has a validation list. When i select a value from the list, i want the value of another cell to change automatically but it isnt working. The list source is pointing to another sheet:
=INDIRECT("DB_DAT!$J$268:$J$275")
I get an error when it tries to change the cell value. Its error no. 1004.
So I've got a drop down list in cell B73 That when I change the selection I want it to copy the cells below it (B74:B94) from one of the charts above it. Through the power of Google I found this: http://www.eggheadcafe.com/conversat...eadid=29484871, someone who had the same need as me, and edited it accordingly to my needs. It however, is not working. I started with Case 1-7 and changed them thinking they need to be the same as the list but that hasn't fixed it either. File is attached, and below quote is what the VBA coding currently says.
View 9 Replies View RelatedI have 13 sheets in my workbook (one for each month plus a GlobalSettings). In each month sheet I want to create a change event that prompts a UserForm when they select "Yes" from a drop-down validation list if it happens to be a month prior to the current month.
This is the code for the change event:
I am building an Excel file that will be used to track information and at the core of it all is a list of people from different offices where the number of people per office can change and/or a person leaves the company and is replaced by another. I'll simply:
Column A Column B
Office Employee
Hamilton Emp 1
Hamilton Emp 2
Hamilton Emp 3
Toronto Emp 4
Toronto Emp 5
Toronto Emp 6
Toronto Emp 7
Toronto Emp 8
Waterloo Emp 9
Waterloo Emp 10
This will be all on Worksheet 'Info'. I have a Worksheet for each Office and named them accordingly. On each worksheet I want to use Data Validation on a column, we will call it 'ChosenOne', set it as 'List' and have the Source pull all the employee names that belong to that office and use them as a selection
ex: Hamilton Worksheet, 'ChosenOne' would show Emp 1, Emp 2, and Emp 3 in the list.
If Emp 3 changed offices to Waterloo 6 months from now I would like to change A4 from Hamilton to Waterloo and the formula would not have to be changed and the next time someone selects 'ChosenOne' it would only show Emp 1 and Emp 2.
Of course this means on the Waterloo Worksheet, 'ChosenOne' would show Emp 3, Emp 9, Emp 10 now.
So basically I am trying to not specify a specific named range for each office and am hoping there is a way to poll information from a Table (or any other tool that can simplify this).
I would be ok with something like:
Column A Column B
Office Employee
Hamilton Emp 1, Emp2, Emp 3
Toronto Emp 4, Emp 5, Emp 6, Emp 7, Emp 8
Waterloo Emp 9, Emp 10
and just move Emp 3 from B2 to B4 but I don't know if a list can be created from multiple items in a single cell seperated by a , or ; or :.
I have a sheet which autofilters according to a validation list in cell E2 using the code.
I have then used subtotal arrays to calculate the mean, median, max, min and total count for whichever values the filter shows.
What I want to do next is have a code which will select each possible option from the validation list (triggering the autofilter) and copy and paste the values from each dependent formula into a new sheet.
Attached File: dummy 1.xlsm
I can't seem to find a way to make a data validation list automatically show the first item in the list rather than showing blank.
View 10 Replies View RelatedI'm trying to use data validation to restrict the user to only selecting values in a list which I create. Right now, the list is a named range. I'd like to get rid of the range and just use a named list. I create a name using the following as my list.
Insert > Name > Create
Name: Fruit
Refers to:
banana,apple,orange
When I try to use the name Fruit in my data validation, I get the message "The List Source must be a delimited list, or a reference to single row or column." I thought my name "fruit" was a delimited list.
I'm looking for a way to get a unique list from a column to a data validation drop down list. Any fancy formula or vba script to create a UDF which. Does this?
View 5 Replies View RelatedI have a List of Different Fruits in Cells A1 to A5
Apple
Banana
Orange
Strawberry
Cherry
And I use data validation list in 5 different cells from Cells C1 to C5 then in every cell the list will show all the fruits,
But I want that if I select Any Fruit in cell C1 that should not be included in the remaining 4 cells, and the fruits selected in Cells C1 and Cell C2 should not be included in the remaining 3 cells and so on....
I Used the formula
=IF(C1=A1,OFFSET(A2,,,COUNTA($A$2:$A$5),1),0)
But this works fine if I select Apple in the Cell C1, then the List of C2 Shows all Fruits other than Apple, But if in Cell C1 I select any fruit other than Apple it does not work... (Using Excel2007 & Win XP)
I am attempting to use a named range as my Validation data source but have two questions:
1) It seems when you use a named range as a source the user can enter in any data they want in that cell. I really only want the user restricted to the list I give them. If I enter the list manually in the source box then it works but I really want to use a named range
2) My range is fixed to 10 cells and the user can enter in as many variables as they want (up to ten). When a user clicks on one of the cells that the validation is set to read the range the user has to always scroll to get to the top. When selecting the cell Ecel always defaults to the bottom choice in the list. I want to minimize the VBA because Mac users will be using this and VBA seems to be touchy with macs.
I would like to exclude blanks from the source of a validation list, I have found a really neat solution for vertical list in this topic, I have modified it, but I have missed something, the formula is not working.
View 2 Replies View Relateddear....can i set width in list box data validation (width in cell i set short).thanks
View 9 Replies View Relatedis there a way to force so that a cell value can be only of what a validation droplist offers, hence users can not enter their own values and have to use the droplist for cell content selection.
View 2 Replies View RelatedI am trying to make some of my data validation dropdown list boxes narrower. I was told one way to do that is to use combo boxes. (If there are any other ways, please let me know!)
I don't think my combo boxes are working. I had tried to make a combo box appear in each data validation pulldown cell by using this tutorial:
http://www.contextures.com/xlDataVal10.html
I followed this tutorial word for word, but when I try to test the code at the end by double-clicking on a data validation pulldown cell, no combo box appears.
I need to have a drop down list which displays a different set of values depending upon the value selected by a previous drop down list. ie. (drop down box 1)= x, y, z. (drop down box 2)= either x1, x2, x3, or y1, y2, y3, or z1, z2, z3. I can produce a single drop down box thats not a problem but linking several drop down boxes is beyond me .
View 4 Replies View RelatedI am using Data Validation on some fields to create a drop down list from a named range! These fields however allow you to enter values that are not in the list.
Is there a way to make the cell have to be an entry from the data validation list?
[URL]
I had this posted in Formulas and functions
I got some code from here on how to display all graph names in a List box and choose which ones to print, but i think some syntax is missing and i'd like to print the charts i've chose, not diplay the msgbox
Private Sub CommandButton1_Click()
Dim intIndex As Integer
Dim strMsg As String
For intIndex = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(intIndex) Then
If ListBox1.List(intIndex, 3) = "TRUE" Then
strMsg = strMsg & "Chartsheet " & ListBox1.List(intIndex, 1) & vbLf
Else
strMsg = strMsg & "Chartobject " & ListBox1.List(intIndex, 0) & " on sheet " & ListBox1.List(intIndex, 1) & vbLf
End If
End If
Next
If strMsg <> "" Then MsgBox strMsg
End Sub
I am trying to use combo box to display my data, here are the details. I am having 20 Departments, and have Budget and Actual values for those Departments for each month for the year 2008, tell me the logic to use combo box as dropdown list for Dept. and how can i get values in first 5 to 10 rows after changing the Department in Dropdown list(Combo Box)
View 5 Replies View RelatedI have a list for example with two variants "YEs, "NO"..
Is there any possibility to choose YES or no in any cell an reflect the same value in another list on another sheet.
I'm not sure if its possible to do what I want. I'm trying to do it without VBA as my users might have a high macro security setting.
I have a validation dropdown in A2 where they pick 'yes' or 'no' for FLSA. I would like B2 to be a dropdown (validation, listbox, whatever) that would change based upon A2. If A2 is 'yes' I want to use the data in A5:B7 and if its 'no' use A10:B12. Ultimately I want B2 to equal one of the numbers from A5 - A7 or A10 - A12. However when they click on the drop down they see the combo of column A and columb B as column A has no meaning for the user.
say i have a range of name called Red_Fruit, Blue_Fruit
and i have a box to input the color of a fruit
I want to be able to select a list of fruit based on the color chosen
How would i call back the name if only part of the name changes?
If a specific word is entered in a cell I want a list to show in another cell. This is better explained in an example. In cell A2 I have a validation list of names, eg. Mary, Mary Birth, John, John Birth,. . .) In cell N2 I have another validation list of places , eg. Ireland, England, Wales, USA, Scotland, . . )
In A2 if a name with Birth is chosen then I only want them to be able to choose from Ireland or England.
