I have a spreadsheet with columns A through AD; values are updated through a query download and the number of rows will vary depending on the date range of the query.
Column AD is a validation list - is it possible to set this up so that the dropdown list will show only if there is a value in Column B? I can get it to work with this formula: =if(B12="190170",AdjustedLeadTime) where AdjustedLeadTime is the name of my validation list, but this is specific to the value of 190170.
I want to set this up so that if B12:Bxxx is equal to any value the validation list will show - but if a cell in B is blank, the validation list won't show up.
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.
I'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 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
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?
I 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.
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.
is 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.
I 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
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.
Sub AddNewRow() Range("a65535").End(xlUp).Offset(1, 0).Value = Range("a65535").End(xlUp).Value + 1 Range("a5", Range("a65535").End(xlUp).Offset(0, 4)).Borders.LineStyle = xlContinuous End Sub
the problem is that I have a validation list on this spreadsheet that does not copy to the new row. I have tried using the macro recorder, but haven't been able to get it to work. I have not been able to find anything with Mr. Excel that specifically addresses this. Can someone point me in the right direction?
the validation list is on a separate worksheet, and is called Performance Factor. there are multiple worksheets and each one has a control button that when clicked on will run the macro to add a new row. once up and running, this workbook will be shared.
I'm using Excel 2000, and have not posted this question anywhere else.
The idea is to create a liquor order sheet which takes items and prices from a liquor inventory workbook, which is part of the same file. i'm sure that you can do it all fancy with a macro, but unfortunately i'm not super experienced with excel. so i helped myself with what i know a bit -> drop down lists. I created an extra sheet which has 4 ranges on there... the liquor type (liquor, beer, wine), and the 3 sub classes for each type (red/white/blush/sparkling for wine, draft/import for beer and so on and so forth). On the inventory i named the range of all white wines "white", of all red wines "red" ..
On my order sheet i created a drop down list for type and created 2 more via "indirect" .. so when you select "wine" in the first one, the 2nd one will ask you for white/red/blush/sparkling and the last drop down then for the exact item according to the range on the inventory.
Hope what i did so far is understandable ^.^
Now my problem is that i want the unit cost to be copied when an item is selected. So you select "beer" in A1, "import" in A2, "corona" in A3... and the price for corona (which is written in a cell on the inventory sheet) should show up in A4.
I attached a screen of the inventory sheet so that you can see how it is structured.
Hello, could some one please help me with the following:
I have created a drop-down list for the range : C3:C65000 using Excel 2003 and Windows XP Pro. I did this using Excel's Data Validation Tool. The settings I used are as follows:
Allow: List Source: =GROUPS Check Box Ignore Blank is Ticked Check Box In-cell dropdown is Ticked
GROUPS = GROUPS!$A$2:$A$29
Now all this works well, such that when I click in the cell range : C3:C65000, then a dropdown list appears - showing a list of all my groups.
However, when I click on any of the dropdown lists in this range, the width of the dropdown list is only as wide as the column. Column C has a width of 20.
What I need please is for the dropdown list to be as wide as the list of information showing in the dropdown list. If some one could please explain on how I can achieve this - that would be great.
I set update the validation list, calculate the spreadsheet copy to another location and then want to do he same for next item in named range. The named range for the list in Validation box is Mlist, and the cell being changed in the report is A2. The below code works for the for what I need but the list is 52 names in worksheet lookup.
Sub Managers() Sheets("Lookup").Select Range("X2").Select
I want this first validation to set for the next list, so if IRS was selected then the next validation will only look at Suite#1 or Suite# 2(C16:C17)
The third list I want auto created from the second list. Example Suite# 1 picked from second list, so the third auto generated list would output Siemens(B16)....
I am trying to check what was selected from a validation list range. I need to put up a message box if a certain selection is made. I will try to give some more explaination and detail.
I have a 'Labor Type' cell that has a validation list based on the 'LaborType' range that contains things like T&M, PM, Claim*, Quote, etc. When Claim* is selected, I want to put up the message box reminding the user to enter some additional information.
I have tried a few things like Worksheet_SelectionChange that I can't seem to get to work. I admit it has been a while since I have used a function like this (and never with a list).
I have a workbook that uses data validation from a list. The same list is used in several of the individual sheets. Can one use just one list for different sheets, as I'm having to create separate list for each sheet, which when an overall change needs to be made it is easy to miss one.
I created a spreadsheet with a list of cities and attractions in those cities. I want an attraction drop-down menu (cell validation) to be dependent on the city that’s listed in the cell above it. I used the below formula: =INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($H$1,"-","_"),", ","__"),". ","__")," ","_"))
This works fine on cities with a dash in them (McGhee-Tyson) or just a normal name (Washington, DC). However, I cannot get the attraction drop-down to work if I select a city with a period (St. Louis, MO) or a space (San Antonio, TX). Am I missing something in the formula? I attached a spreadsheet as an example. I use Excel 2007, but saved the spreadsheet in Excel 97 – 2003 format.
I have a cell which contains a validation list with about 350 names to select from. When the user is selecting the name they wish to make changes upon, is there a way to allow the user to hit say 'H'f and skip down to the last names beginning with H?
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)
find attached the Report file. I prepared two sheet in this file and also add my question in the file. my question is about validation selection and get data from other sheet.
