I'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.
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 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 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.
See attached. I cannot get my indirect function to work in Cell K2 based on cell J2
Data for the Validation is in cells W1 to AC14
I want to do data validation in Column K based on Column J, but cannot get the indirect function to work to past into the data validation tool
I have named all my ranges.
AM Call Log.xlsm
Is there any way when using dependent validation lists, that once a selection is made in one list, the next list will automatically default to it's first item?
So in the attached example, if Activity2 is selected in cell B7, then TaskC would automatically appear in cell C7 but the user can still choose a different value if necessary?
Here is what I am looking for I have a 3 list first is 'start date', 'end date' and third is 'Product'.
The LoV's are: .....
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 have a worksheet (mileage) which has a client column (column B) which is validated so that the user can only choose clients from a dropdown list. The dropdown gets its values from another sheet (data) in the same workbook. In the data sheet each client also has a recharge flag (1 or 0) in a column beside it.
In the mileage worksheet column E is ‘mileage’ and column F is ‘recharge mileage’. What I need to do is create a validation rule or macro that only allows a user to input a value into the recharge mileage column if the client whom they selected from column B has a recharge flag of 1 (i.e. they are a recharge client). If they try to input a value without the client being a recharge client (or without selecting a client) it should give an error message.
I am having trouble with a dependent dv list. Please see below:
=INDIRECT(IF(I5="Support",Internal_Cost_Centres,Project_Codes))
where
Support is a named list from the parent dv list
Internal_Cost_Centres is a named list
Project_Code is a named list
When I select Support from the parent dv list, it will return the list named Internal_Cost_Centres, however, when I select something else it doesn't work.. there are 3 options in all on the parent list (Support, Production, Project) and whether Production or Project selected, I want it to return the same list - Project_Codes.
I've looked at the data validation tips and understand how to make a second dropdown dependent on the 1st, but how about a 3rd dependent on the prior 2 selections and so on.
I would like to have dropdowns based on a dataset of 4 columns (Location, Department, WorkGroup, Employee) that frequently gets updated. I would like to have the user select a location then a department then workgroup and employee. I would then fill in a worksheet with other pertinent information. I can get the 1st two dropdowns to work, but haven't found the key to being able to continue to drill down based on the 1st two options.
I have data validation in column A, I want column B to be dependent on contents of column A i.e. If I enter "X" in column A then column B validation list should change. I tried using "if" but it can take only 7 criteria. I want to use more.
View 6 Replies View RelatedI am unable to use dependent data validation lists using the INDIRECT function when the initial named range is a dynamic one.
See attached.
The named ranges 'Men','Women','Children' are all dynamic based on number of entries in each column order to accommodate a growing list, whilst also not having blanks in the dropdown (hence I haven't used entire column ranges). The named range 'test' is a static one.
Column H has a dependent data validation based on entry in Col G. This works for the static list, but not the dynamic one!
I have three individual lists and I am using a formula like this for each of them =OFFSET(Table1,MATCH(F15,Table1,0)-1,1,COUNTIF(Table1,F15),1)
for my final cell I need to create another data validation list which is depenant on the values selected in the previous three lists.. how I would alter the formula to allow me to do that? I tried using and after the match to match all three tables but it never worked
I have an Excel file with two different visible sheets (dashboards), each with different types of charts, which are pulling from the same data tables on a hidden sheet. I have a data validation drop down list on one of the dashboard sheets, which lists 7 items. Once one of the 7 item is selected, both dashboards update, which is what I want. However, I'd like to be able to have duplicated, related validation drop down lists. So if someone is looking at the first dashboard sheet and they select a new item, when they go to the second dashboard and see that same item, they could on that second dashboard select a new item without having to go back to the first dashboard.
I'd provide an example if I could, but cannot. I've tried searching on the forum for something similar, but most often the topic of dependent data validation is on two different types of drop downs with the second being dependent on the first, whereas I'm looking for two drop downs that are interconnected and can update in sync.
I have a list drop down that is dependent on a first list. The first list has numbers, spaces, and "-" at the beginning I need to get rid of to make it a valid name to reference. The "Substitute" function can't be nested enough times to make this work for me since I have a fairly lengthy list for the independent column which has differing numbers at the beginning. The first two examples of the independent drop down (which would dictate the second dependent column and drop down) are:
00 - Preconstruction
01 - General Conditions
I would like to name these something like "Preconstruction" and "GeneralConditions" for valid naming convention.
Second thought:
If character removal isn't the most efficient or possible at all, is there a combination of reference functions that could make this work? Ultimately I want to use these 2 drop downs for reference functions on a second worksheet.
I am working on a project where I have a data validation list box in column A B and C on the same row. The selection in column A determines what can be selected in column B, and column b effects column c selection. The data from the list is located in another sheet and dynamic named ranges.
The data validation source is as follows:
Column A:
=ProjectNoList
Column B:
=OFFSET(ProjectNoList,MATCH(C11,UniProjectNo,0)-1,12,COUNTIF(UniProjectNo,C11),1)
Column C: =OFFSET(ProjectNoList,MATCH(D11,OFFSET(ProjectNoList,MATCH(C11,ProjectNo,0)-1,2,COUNTIF(ProjectNo,C11),1),0)-1+MATCH(C11,ProjectNo,0)-1,3,COUNTIFS(DisciplineCode,D11,ProjectNo,C11),1)
This works fine, however I need the drop down menus to be longer as there can sometimes be a lot of data to scroll through. To do this I have used the instructions at Excel Data Validation Combo box Click to make a combobox data validation.
It works fines for column A, however I can't get the list for column B to populate in the combo box.
The code looks like this:
Code:
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
On Error GoTo errHandler
If Target.Count > 1 Then GoTo exitHandler
Set cboTemp = ws.OLEObjects("ComboName")
On Error Resume Next
If cboTemp.Visible = True Then
With cboTemp
.Top = 10
.Left = 10
.ListFillRange = ""
[code]....
I suspect that column A works because the data validation formula is only referring to a named ranged, ProjectNolist, however columbs B and C use a few in a formula.
Code:
.ListFillRange = str
is empty when column b is selected
is it possible to get the data validation list from column b and c into a combo box and if so how?
I am using Excel 2007 and I've been working with a spreadsheet that utilizes several Data Validation to allow users to make different choices and depending on those choices the next options in line change. My problem is that I have so many different options, so long of formulas, that I run out of space to type in formulas in the DV source field. One way I get around this is to drop down to the next cell and start again, but I really need all the choices to stay in one row. Somebody suggested using VLookup, but I am not sure if that is the right path to take because a few of the final drop down choices have multiple options, and I'm not clear on how to use VLookup.
I am strongly considering using a Combobox Form Control or Combobox ActiveX control because, from what I have learned, they wouldn't be restricted by formula space. However, being fairly new to Excel I do not know what the codes would be to allow the comboboxes to change options based on previous selections. I've searched the message boards extensively and haven't been able to find an answer.
I found a code on [URL] that claims that it will do what I want to achieve. I tried it but it is not working for me. Here is the code
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim str As String
[Code]....
How to verify that this code is a working code and not just bogus one?
I'm using a script that when you double click on a cell containing Data Validation > List it changes to a combo box that has an autocomplete/increased columns view...It's working great, however there are two small modifications I would love to do and am not having much success with. Currently, you double click the cell w/the Data Validation > List in it, it then changes to a combo box and you can auto complete using the keyboard or select the dropdown and choose from an increased list. However, I would like to be able to simply single click on the cell, have it change to the combo box and auto expand the list if possible.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim wsList As Worksheet
Set ws = ActiveSheet
Set wsList = Sheets("Rep Assist Report")
Cancel = True
Set cboTemp = ws.OLEObjects("RepName1")
On Error Resume Next
With cboTemp
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
On Error Goto errHandler
If Target.Validation.Type = 3 Then.....................
I am using excel 2010. I have 3 separate form control each with its own macro. I would like to create a combo box with a drop down with a list of each form control case and be able to perform the same function as the individual form control when a specific name is chosen in the list of the combo box.
View 3 Replies View RelatedIs it possible to use dependent combo boxes in user form?
For example I have three country name
USA Germany Italy
I want to choose one of them and then my choose be restricted to chosen country cities
When I select Italy in combobox1 i see just Milan and Rome in combobox2
If I select USA I see new york Los Angeles and ...
besides
I wants to see each of these dependent city telephone codes in textbox2 ( kind of vlookup that work with dependent comboboxes).
I have a list of departments on "Employee Data" sheet on column A and list of staff names on column B. It looks like this:
DEPT FULL NAME
pdi Bob Tan
pdi John Christian
aftersales_bikes Jim Yeo
I have 24 departments in total and 300 over employees in mt employee data sheet.
On "Jan - June 2014 Training Hours" sheet, when employees attend training courses, I will have to update what courses they went for, no of hours etc.
My problem is:
I would like to select the department (using combo box for the auto complete function) and when department is selected, the staffs in that particular department will show. I have attached the file for your understanding. Currently, I am using data validation with indirect function in that file but would like to switch to combo box.
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 Related