I think this is fairly simple but I don't know what the problem is. I have a worksheet with some validation lists. I found that I am able to type values that are not on the list into the cell without getting the error message. Does anyone know why this is happening? How can I fix this? (I have the show error alert checkbox checked on the data validation menu)
View 9 RepliesI'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.
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.
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 several data validation lists that are driven by named ranges. These ranges include blank cells in order to allow for expansion of the list easily - without updating the named range itself.
Seems like because of these blanks rows...excel is accepting any value. The list is visible and can be used, but the user can also enter in a value not on the list. (when I remove the blank rows from the range, the validation works properly)
data validation to display a list of values. The problem am facing is .. this list has a lot of names & it becomes difficult to scroll through when i need to search for a particular name.. Is it possible that when I press a key on my keyboard, the name starting with that letter is highlighted in the drop down list.. This is not happening automatically using data validation... Is there a work aorund for this?
View 2 Replies View RelatedI have data validation in cells A1:A50 which allows to select values from a list. There are about 50 items in the list that can be selected. Is it possible to have the user select an item only once i.e., if an attempt is made to select an item thats already selected it show an error message.
View 3 Replies View RelatedIs it possible to have a dependent validation drop box that under certain conditions automatically produces an actual value in the cell instead of a drop-down list to choose from? For example, if I choose “Holy Avenger” in the first drop box, and the value for that choice in the second drop box is always a “2”, can you get it to auto-populate in the cell occupied by that 2nd drop box (cell B5 in the attachment)? Or, are you restricted to using Lists only, meaning that you could create a List that only has the value “2” in it, and the user would be required to choose the “2” in the drop box?
View 2 Replies View RelatedHow 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 RelatedI've found a nice looking formula for looking up multiple values from a Data Validation List which you can find here.
Unfortunately I dont know why im getting a #REF! error for one of my spreadsheets. Here is my Formula {=SUM(VLOOKUP(A2,D:D,{6,7,8},0))}
A2 is the Data Validation drop down list that has most of the Values (Letters & Numbers) I want to look up. D:D is where it will find the Values (Letters & Numbers) and {6,7,8} is the currency I want added up and displayed.
I've tried this formula on other spreadsheets with success, but no success with the spreadsheet im working on currently.
How to have a validation list (drop-down) from a single-cell in which values are separated by a comma.
Example I have.
I have a wine list which have many categories of which
COL A / COL B / COL C
Wine Name / Wine Producer / Vintage
I wrote a VBA code to look-up duplicates names and give the producers that make them. The names of the producers will be in an invis cell as Comma Separated Values.
For example: Wine 11 is made by producer X and Wine 11 is made by producer Y. I will have X,Y in a cell which need to be in a drop-down menu when Wine 11 is selected.
I got everything done. The only issue is to get the list. The problem is that the drop-down list shows X,Y as a single component and not as 2 drop-down components. If i were to write down X,Y in the validation list tab, it will show it as 2 components but referring to the cell doesn't.
I have the following macro that exports pdf's for every value in a validation list. Currently it's setup where I have included each value in the macro. This is fine with just a few values but I am about to add 900.
My question is, is there a way to write the macro so it exports a pdf for every value within a dropdown list until it hits a null value instead of what I have below so i don't have to repeat everything for every value in the list?
Here is what i currently have.
Sub Export_MarketSpecific()
'
' Export_MarketSpecific Macro
'
'
Sheets("Home Page").Select
Sheets("MOA-Page 1").Visible = True
Sheets("MOA-Page 1").Select
Sheets("MOA-Page 2").Visible = True
Sheets("MOA-Page 1").Select
Range("D2").Value = Range(Range("D2").Validation.Formula1)(2).Value
[Code] ..........
I have a data validation list where more than one value should show the same list. Instead of make multiple range, I want to make it so if any of the values are in a cell the data validation list will show the list that goes with those values.
View 4 Replies View RelatedIn cell H5 there is list of dates and in H7 there is codex, based on these two conditions in H11 a data validation list should be populated from the code_sheet using column B. The populated list should be unique entries, as the column B in code_sheet has duplicates.
View 9 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.
A2's validation is dynamic as it's selectables varies based on the selection made by the A1 Validation. (=indirect(a1)).
My problem is this....once if have selected from both validations...if I go back and change the A1 validation to a blank (or empty value) or clear the contents on that cell....validation A2's value remains as it was. I would like it to recognize that A1 is blank and also become blank (or goto an empty value).
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 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'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 Relatedi 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 Im al a loss.
View 3 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 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?
I have 2 columns First Name & Surname. What I want to do is create a data validation list on the surname which results in the 2nd data validation list only showing the first names which link to one of the surnames.
i.e. If I selected Smith in the 1st validation list then I would only like to see 'Paul' as an option in the 2nd list
First Name
Surname
Paul
Smith
Paul
Jones
Tony
Phillips
I'm trying to create an excel template to log tickets , which should allow me to categorize the tickets in two to three levels. For ex : In the spreadsheet attached , in I4 I have done the first categorization as Fruits , now J4 should populate the list based on I4 selection. I have attached a sample spreadsheet for better explanation.
View 2 Replies View Related