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
i know it is possible for a selection in a drop down box to determine another field using the INDIRECT function in validation
eg - 1st drop down box - Football, Rugby, Cricket
2nd drop down box (if chosen football) - displays list of football teams
2nd drop down box (if chosen rugby) - displays list of rugbyteams
2nd drop down box (if chosen cricket) - displays list of cricket teams
but is it possible for the 1st drop down box to determine what is available in a range of other drop downs?
eg - 1st drop down box - Football, Rugby, Cricket
2nd drop down box (if chosen football) - displays list of football teams
3rd drop down box (if chosen Man Utd) - displays list of Man Utd players
troubleshooting dependent validation list formula. I found the Ozgrid instructions for the Dependent Validation Lists to be verfy informative and I downloaded the "MatchingLists.xls" to use as a starting point.
My challenge is this: The MatchingLists.xls example does not take into account that there may be MULTIPLE dependent lists that have spaces, numbers and other unique characters in the list's source celles. Therefore, I have decided to reference a NameLookup (named range) that has an abbrevieated name for each each item (without numbers & spaces) I am nesting VLOOKUP within the INDIRECT formula to achieve the results I need - but for some reason it fails occasionally and I do not know how to troubleshoot!
Data Validation Source =INDIRECT(VLOOKUP(SUBSTITUTE(E5," ","_"),NameLookup,2,0))
Excel's Data Validation simply returns the message "The source currently evaluates to an error". This is frustrating becuase it is not consistent. It works with some lists and not with others. How do I troubleshoot???
My worksheet is attached for your reference. The source data for the dependent lists is on the Lists sheet. The dependent drop-downs are in columns E-H on the drop-down sheet. The formula that I am having trouble with is in "Subcategory" (column F). For some reason, I cannot choose category "26_Heat_Exchangers" to populate the subcategories. There are a few other categories that this formula breaks on and I don't know why. Please tell me this is not some crazy limitation with Named Ranges.
I am using a data-validation-list with =INDIRECT(B7) as the source. It works as expected except for after the file has been closed and reopened. When it is reopened, the source is changed to =INDIRECT(#REF!).
I have tried =INDIRECT('Worksheetname'!B7) but same result.
I have a worksheet that uses the INDIRECT function as part of cell validation to generate a custom 'name' range, this name then references a bunch of sheets that contain the actual range where the values for the validation list are stored. For example: ValidationExample.xlsx
Name = Color
Values = Blue, Red, Green
Name = Shape
Values = Square, Circle, Triangle
So what this allows me to do is in the first cell, I can define two options such as Color and Shape. Once one of these is selected, the next cell will have a formula for the validation as "=INDIRECT(A1)" then I will define two named ranges called 'Color' and 'Shape'. This will then show me the list of items in the cell based on previous selection. An example of this is attached to this posting above.
When the formula is written into the validation, an error message is generated saying that the formula will generate an error. Even with this message, the method works effectively to provide a blank list if nothing in the first cell is selected, or a list dependent on the selection of the first cell.
In this case, what I want to do is generate a macro that will populate the validation for a cell when new records are added. This won't always copy down from previous cells because the way I add records is through use of a macro and I generally find using the format painter in vba to be a fairly sloppy way of getting formats from other cells. So I go through a series of validation additions to each cell in the worksheet to get this validation created (I have no written this into the workbook attached).
VB:
Dim i As Long
Dim lastRow As Long
Dim sht As worksheet
[Code] .....
As I read in previous posts, Formula1 cannot contain an actual formula, only names and lists of items. Since each of the cells will have a changing reference, thus the INDIRECT function would need to change to reflect this, The 'Secondary' name consists of the following:
"=INDIRECT(OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN()), 0,-1)))"
Which of course does not work due to the error when using INDIRECT in the first place (though the Address() function appropriately finds the cell with the validation in it's appropriate address). I can think of other ways to do this, such as having validation lists change in with VBA on a SelectionChange or Change event, but that's a lot of code that is subject to change when the named ranges have changes to them (which is fairly frequent). Is there any way to get the .Validation.Add method to ignore errors in the name supplied to it.
For reference, my code is this:
VB:
Private Sub cmdRefreshValidation_Click()
'Re-enters validation parameters to all cells to allow selections
Dim i As Long
Dim lastRow As Long
Dim sht As Worksheet
[Code] .....
I have a dynamic validation list (see attached) which will not accept the indirect function as a formula in the listbox.
Using the named range on its own works fine, but using the indirect generates an error.
I've attached a sample file of what I am trying to do.
In row 2 I have data validation in the form of lists. Below row 2 is the data.
Cell B2 has an indirect expression referring to cell A2. In other words, B2 is dependent on A2 (cascading)
What I would like to happen is when a new selection is made in cell A2, B2 populates with "Choose platform", instead of keeping the old information. The drop down populates with the new data but cell B2 still displays the old data.
Book2.xlsx
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 am trying to develop an Indirect Indirect Validation drop down list. Example, Building - Floor - Room, i.e. Select Building from a Validation drop down list. Then based upon the Building selected, select only the Floors applicable to the Building Selected. I am able to achieve this via an Indirect Validation drop down. However, when I attempt to then select the Rooms applicable to the Floor of the Building I selected, I can not produce an Indirect Validation off a previous Indirect Validation.
In the attachment, I have used Plant - Location - Room. I have name ranged the selections, and have used Validations Lists for Plant, and Indirect Validations for Location. The error occurs where I attempt to do an Indirect Validation for Room.
I am trying to arrange a dependent drop-down list and have the basic listing working. My problem however is that when i pull data from sheet "Mon" for column C using indirect it works but when I try to do the same thing for column D i get the same data as column C. How do i get indirect (or offset if it works better) to allow me to reference the alternate data 1 column over?
View 3 Replies View RelatedI have a competition entry form for an association I belong to that I'd like to add two drop down menus to so that entrants can select their club name and associated team names for that club.
In a worksheet behind the form I have a list of clubs in column "B" ("A" has the ID number in it as the table is from Access - don't ask!!) and the names of teams associated with that club going across the sheet beside the club name in individual Name Ranges. (Up to 30 Team names in Dynamic lists
=OFFSET(TeamsTest!$C$2,0,0,COUNTA(TeamsTest!$C$3+TeamsTest!$C$3:$AZ$3),1) )
On the form I have managed to make the first drop down so the club can select its name from a list by using Data Validation and referring to the Range Name (ClubLink - =OFFSET(TeamsTest!$B$2,0,0,COUNTA(TeamsTest!$B$2:$B$200),1)).
I have tried using an INDIRECT command to create a second drop down which will display only the team names associated with the Club selected in the first drop down, but can't get it to do anything - no drop down appears when you click on the drop down arrow at the side of the cell
The data Validation for the Team Name drop down is =INDIRECT(SUBSTITUTE($C$12," ","")) where C12 is the Club Name cell.
Why I can't get the Team Name cell to show the names listed beside the Club selected in C12?
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'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.
I am setting up a spreadsheet for user data entry. I have one sheet set up as a template to enable users to copy the required data header cells to subsequent sheets and (the problem) - to different locations on the subsequent sheets. The template is using validated lists with the criteria drawn from the cell/list directly above the current list. For example, the cell in R11C2 is validated/refering to the range: =Campaign
The cell directly below this is validated/ filtered by: =Indirect(R11C2). This works great in the template, or any subsequent sheet in which the cells are all located in the same row/column. However, when the template is pasted in a higher row, the Indirect refers to R11C2 rather than referencing the cell directly above.
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 can use a dynamic named range and a direct reference to the name and the DV works.
I can use a standard named range (not dynamic) and an INDIRECT() reference to a cell with the name of the range in it and the DV works.
But if I try to use a dynamic named range and an indirect reference to a cell with the name of the range in it, the DV fails.
Any workarounds out there? This project includes a LOT of DV lists, getting them to self-maintain is important if I can do it.