I am trying to create validation drop down list for a cell, however I want different drop down lists which are depenent upon another cell.
in Cell A1, I want the options "A", "B","C" etc
If cell A1 = "A", then drop down validation for cell A2 = 1,2,3,4,5
cell A1 = "B", then drop down in cell A2 now equal 6,7,8,9, 10
cell A1 = "C", then drop down in Cell A2 now equal 11,12,13,14,15
Say in A1 i have either Yes or No, in B1 i have a dropdown validation from a list if A1 is Yes and "n/a" if A1 is no.
I thought if i renamed my list validation to "Yes" and labelled "n/a" as "No", then using =INDIRECT(A1) as my list validation would do the trick.
Situation:
I have two drop down lists 1) Country and 2) States/province
Country has list: [US, Canada]
If "US" is selected, [Arizona ,Florida, NewYork] is listed in the 2nd drop down list
If "Canada" is selected, [Alberta, Ontario, Quebec] is listed in the 2nd drop down list
Scenario:
First, I select "US" and choose the states to "Florida"
Next I change the country to "Canada" and forgot to choose province
Then, the 2nd dropdown list is changed to [Alberta, Ontario, Quebec],
BUT the current value is still "Florida"
Probem:
Now I have "Canada" and "Florida" selected in the sheet
Question:
If the primary list is changed, can I make the 2nd drop down list to show a default value (e.g. blank or the first entry i.e. Alberta)?
I've got a password protected spreadsheet with only certain cells unlocked to allow users to complete them. I need the following to work on Excel versions 2003, 2007 & 2010.
Cell E3 has a drop down list with types of car to choose from. One of the car types is "Pool_Car".
Cell D12 should only be used if "Pool_Car" is selected from the drop down list. If any other car type is selected, and the user tries to enter a figure in D12, I need an error message to appear.
E3 & D12 are unlocked cells.
I'm happy for D12 to become locked & only allow it to be unlocked if "pool_car" is selected, but I've looked elsewhere, but can't find VBA or data validation to work.
I want to populate the value of a specific cell based on the choices made from two other dependent validation lists.
I am attaching a workbook that explains what I need to do.
I have set cell validation to use a named list and checked 'In-cell drop-down'. The validation works but the drop-down is not working.
View 9 Replies View RelatedFor our attendance register, the master data table holds a list of all club members (one member per row).
The master data table consists of formula columns, data columns, and two data columns with drop-down list validation.
I'm using this
I have been searching on this for a little while now but have not been able to find a solution to my problem. I have been asked to create a series of dependent dropdown lists using excel so that we can use these for inventory imports into a asset system. The catch on this is that nobody here really has any idea on how to make these dependent lists reference each other correctly. The reason for that is because the fields all have spaces in them and I do not know how to get a list name with a space in it or to create the proper translation for it. An example of this would be something like what I have written in below ....
View 13 Replies View RelatedI am trying to put together two combo boxes, Combo1 and Combo2, the content of Combo2 will depend on the selection made in Combo1, for example Combo1 will have 10 items, once an item is selected Combo2 will have different sub selections relating to that item. I know you can do this via Validation, but the problem with that I have about 4000 options in the combo1 and about 60000 in the second, here is the structure of my data:
First Option /Second Option
Option1_____1.1
Option1_____ 1.2
Option1_____ 1.3
Option2_____ 2.1
Option2_____ 2.2
Option2_____ 2.3
Option3_____ 3.1
Option3_____ 3.2
Option3_____ 3.3
My ultimate final result should look like this, two drop down menus once Option1 is selected second drop down form should display these options:
Option1 /_____ 1.1
____________ 1.2
____________ 1.3
see attachment for complete clarity
I'm trying to make a number of cells dependent to a drop down list. However, these cells would also be drop down lists and while they'd show a value automatically determined depending on the drop down list, the user would still be able to choose another value.
Aluminium
Coated steel
SS304
SS316
This would be my initial drop down list.
In the same sheet, I could choose the materials for the machines shaft, fasteners, anchors, chains, sprockets, nozzles, etc. These materials would be, again:
Aluminium
Coated steel
SS304
SS316
in 90% of the cases, having the machines frame at SS304 would mean the rest of the materials would also be SS304. However, depending on the needs, the anchors could be SS316. So basically I'd like that the options all be in the same material as the frame, but with the possibility of choosing another material through a drop down list.
My problem is a dependent drop down list that keeps the previous value when a different value is picked in the first drop down list. The dependent drop down needs to change to empty/blank.
The first drop down list is in cell C3.
The dependant drop down list is in cell C4.
The source in C4: =INDIRECT(VLOOKUP(C3,allnames,2,2))
I had a look at various answers on the internet but somehow cannot get it to work (and that is more of a reflection of my own skills than the writers of those answers!).
The only thread I could find on this topic was not resolved. I want to reset the value of a dependent drop down list (in H3) to the first valid choice when F3 is changed.
I have the following code that initially worked but no longer does.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Goto errHandler
Dim DLRng As Range
If Not Intersect(Target, Me.Range("F3")) Is Nothing Then
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
Set DLRng = ActiveWorkbook.Names(Target.Value).RefersToRange
Me.Range("H3").Value = DLRng.Offset(0, 0).Value
End If
exitHandler:
Application.EnableEvents = True
Exit Sub
errHandler:
MsgBox "Could not change dependent cell"
Goto exitHandler
End Sub
I now get the error message ""Could not change dependent cell" every time I pick a different item in the drop down list in cell F3. If I remove the error handler I don't get the error and the code simply doesn't work.
I'm using dependent drop-down lists. In the first box the user can choose a color which drives a set of options in the second box. (e.g. User selects "Red" and in the second dropdown they can then choose "Red A", "Red B", "Red C", etc.). Right now, if you select "Orange", it will still show "Red A" from the previous selection until the user selects the dropdown in the dependent box to refresh the list of options. Is there a way to make it so that second dropdown box will default to "Orange A"?
I tried writing some code to do this, but I can't seem to make it work. I've attached my file.
Now he has another task that I am not having any success with. He has asked if we can make an Excel spreadsheet that allows us to access a list of customer units from a drop down menu which is in another workbook, and once you choose that particular unit, another drop down menu will allow only those customers who are assigned to that unit to be selected from that sub-menu. I hope that makes sense.
Say customer A1 (we will call him Delta Co, 2-1 Aviation) is selected from this first drop down menu (which I haven't figured out how to make yet either). Then this selection would access another worksheet in that other workbook with a new drop down menu with only the four customers in that unit. As an example we will use the names SSG Wilson, SGT Jackson, SFC Jones, and SGT Phillips.
So when the Supply Tech here opens the spreadsheet, the first drop down menu, he will select Delta Co, 2-1 Aviation. Then the next menu will only show four names, and he will select SFC Jones.
Once we get this going, I may be able to figure out the other things the commander wants, if not I will be back.
Is there any chance someone could post an example file for this? I cannot figure out how to set it up.
View 14 Replies View Relatedto make the user experience better by inserting the " Stock Search" sheet to speed up the process. The spreadsheet attached is a simplified version of the current version, where it would normally have many many more sites, areas and models of cars. The existing process means the user has to trawl through the sheet where they are only interested in what sites in their area has stock. I can not change the format of the "Red" and "Yellow" sheets so hoping to insert another sheet to do the work.
I am hoping to have on the "Stock Search" sheet a few drop down boxes.
The user will select
1. Area
2. Type of Colour (the type of colour selected should link with the sheet name in effect, so only stock within the "Red" sheet will be shown if the "Red" colour is selected from the drop down box)
3. Model of Car
This will then give the resulting Store/Stores with stock (value 1 or above) with those conditions met which is showing in the relevant sheet
So as per the attached sheet, if a user selected Area "South West", "Red", "Renault Megane"
then SO10 Clifton 1 and SO15 Yeovil 3 would be shown in a list.
Firstly I have these 12 values as in the attached excel sheet (in the order: FY09, FY09Q4, FY09H1, FY09H2, FY10, FY10Q1, FY10Q2, FY10Q3, FY10Q4, FY10H1, FY10H2, FY11Q1) to be selected as Start Date and End Date. The Limitation on Start and End Date is that the End Date cannot be smaller than Start Date (for e.g.: If start Date is FY09Q4 the End Date can be FY09Q4 and above but not FY09). Now I have a separate set of Initiatives/Objectives for each time period (each Initiatives are marked in excel sheet 2) and Initiatives depend upon Start Date Selected so once Start Date is selected only the related List of Initiatives should be published.
Now the Validation and Naming I have used is not allowing me to use a list name as “FY09” and “FY10” as they are cell references in the Workbook. But I cannot also change the name to something as FY_09 as the data that would be there in the Template will finally be used without editing for upload to an application.
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 workbook with three sheets.
Sheet 1 is a large data array (approx 8 columns x max 400 rows), than contains the inventory and cost information of a list of products in a running cafe.
Sheet 2 is is a recipe analysis tool that I want to be able to use data validations to cost out recipes, drawing information from sheet 1 in three dependent drop down boxes 1- category, 2- supplier, 3- item name, which then returns that items cost per standard of measure.
Sheet 3 is where I have started to try to generate a unique list for category and supplier to assist sheet 2 in referencing from sheet 1.
There is data overlap across the inventory items, with many items having the same category and or supplier.
Examples that I have found online use the index and match function, in conjunction with naming to return the array required for the drop down, however most of these examples I have found contain simple unique lists.
I cant seem to nut dynamic naming, which is integral as the list is expanding and contracting all the time.
So far I have been successful by manually naming a range (not a satisfactory solution as per above) and then using the indirect function to reference that named array from sheet 1.
See the attached example. I have created a dependent data validation. The list available in B2 is dependent on the item chosen in Cell A2.
What I would like to know is how do I expand this validation down columns A and B. I don't want to have to add the validation in each cell individually!
I cannot seem to add more dependents using this code. I think it is in the "If / then / else" structure that it is limited to only those three "groups". But I need to be able to add at least one more dependent list to the code.
View 4 Replies View RelatedI have attached a file where I’ve made some dependent validation lists. When the first box is changes it clears the last 2 with this bit of code
View 2 Replies View RelatedI'm still curious about the sample from Contextures, http://www.contextures.com/excelfiles.html (DV0032 - Dependent Data Validation Combobox)
I have made a modification from its file (sample is attached). First drop list can be autocomplete, but the second cannot. For the comparing, I made row 2 in ValidationSample sheet is my modification, and row 3 is the original.
I am trying to make two dependant lists in Excel. Using data validation lists, is it possible to, for example, select a country from one list, then select a city in that country from another list. For example, if I select France from one list, I only want the list of cities to include French cities, rather than the entire list of cities in the list.
View 2 Replies View RelatedB2 should be drop-down list based on the value in B1.
Example: If Function code is 901, the drop down list should contains 3 values: Warehouse, Transport, QC only.
As we have ~100 function codes, formular with IF that I tried -> Data validation IF(B1="901",901,IF(B1="902",902 .... are not applicable.
I tried Excel Data Validation -- Dependent Lists but it didn't works.
Currently i have a list of cells D7:D19 which have data validation lists dependent on the values in B7:17. When the cell in the B column is cleared the value in D remains. I want it to clear when the value from B is cleared. I currently have tried to following code which works for cells B7 and D7, but how do i apply this to the whole range?
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo sub_exit
If Target.Address = "$B$7" Then
Range("D7").ClearContents
End If
sub_exit:
Application.EnableEvents = True
End Sub
I am needing to create 2 drop downs that are dependent on 1 drop down. I have named lists that are on another worksheet. I've gotten so far as getting the 1st 2 drop down lists work but my third drop down I just can't figure out what the formula needs to be. HELP!! I've been working on this for a week now and I'm losing my mind. I've checked the contextures website and it does NOT answer this question.
View 9 Replies View RelatedI have an Excel spreadsheet with two lists of data, the first is a list of groups and the second is a list of sub-groups, each group containing a number of sub-groups, each sub-group being unique and belonging to only one group.
Elsewhere on the sheet, I have used Data Validation (using 'list') in 2 columns to provide a drop-down selection in cells which the user must complete. The first column refers to the first list (groups), the second column refers to the second list (sub-groups).
However I would like to make the values which appear in the second drop-down list dependent on the value which has been selected in the first. That is: make the user choose a group in the first column and then make the choice of available sub-groups in column 2 restricted to those sub-groups which belong to the selected group.
Is there any way to make Hack#24 work with more than 5 lists? I followed the information in the book and was able to get 5 secondary cells to have a drop down list that was dependent on the primary cell selection (also from a drop down list). But when I tried to apply this to the next six primary and secondary cells it would not work.
View 5 Replies View RelatedTwo of them operate independently of each other, as in it doesn't matter whether a user selects yes or no, those are the two options. The validation list of the third question is dependent on the two others. Essentially if someone selects "No" to the first question, the only option for the third question is "N/A". If someone selects "Yes" to the first question and "Yes" to the second question, then they have the option of a yes/no drop down list. If someone selects "Yes" to the first question and "No" to the second, then the only option is "N/A." Here's my dilemma.
Since what I'm trying to create is going to be incorporated into a financial model that many people will use for to test a variety of scenarios, I'd like to make sure that the first option in the dependent drop down list (in question #3) displays automatically and changes if someone switches back and forth between different yes/no answers.
I've read how to get a dependent list to automatically populate the cell based on the selection in one cell (i.e. if question #1 is a "No", I can make the cell for question #3 populate with "N/A"). But since the validation list for question #3 is dependent on how someone responds to questions #1 AND #2, I'm not sure how to figure that out.