Dependent Validation List Errors With Indirect & Vlookup
Dec 31, 2008
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 want to extract/generate data from a database (same as like we can do with filter or pivot method) with H/VLookup or any other method.
Like: Sheet 1 (Database)- 23-column & 690-Rows having record of 9 Different Groups)
Sheet 2 (Report)- A2 contains List of 9 Different Groups, If I select Group 3 from that list then I want to show all relevant data of Group 3(23-Column) in this sheet.
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 need to create a data validation drop-down that is dependant on what has been selected in another drop-down. However, all the solutions I've come across online so far suggest using the INDIRECT function, which is not appropriate in my case. This is because the data upon which the two drop-downs are based on is itself dynamic. My specific situation is this:
In one sheet, I have a list of university names in one column, and in the next column a list of faculties. The same university can appear multiple times in the sheet (once for each different faculty at the university). This information will be added to regularly, with new universities and new faculties added often.
In another sheet, I want a drop-down in one column that takes its validation from the list of universities. I then want a second drop-down that is only populated with a list of faculties at that university. The INDIRECT solution seems to only work if I can define a static range of cells from which to draw this data. How do I do it in my situation above?
If I go any of the cells in that column, the VBA code page opens up and I get the following message:
Compile Error: Ambigous name detected: Worksheet _SelectionChange
and the following vb code is hightlighted:
Private Sub Worksheet_SelectionChange(ByVal Target As Range
The code I have used is: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count > 1 Then Exit Sub If Target.Column = 13 Then Target.Columns.ColumnWidth = 40 Else Columns(13).ColumnWidth = 20 End If End Sub
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.
Two 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.
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.
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 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.
I have a spreadsheet (Need Data.xls) that needs to be filled out with a couple columns of data.
This data lays within 338 spreadsheets which have many items and may only have 2, or 3, or 50 that belong on my Need Data.xls spreadsheet.
I have a tab in Need Data.xls named "DIR" which has a list of 336 excel files that need to vlookup'd into.(not a separate file) They're all setup with this format:
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 a sheet which autofilters according to a validation list in cell E2 using the code.
I have then used subtotal arrays to calculate the mean, median, max, min and total count for whichever values the filter shows.
What I want to do next is have a code which will select each possible option from the validation list (triggering the autofilter) and copy and paste the values from each dependent formula into a new sheet.
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.
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.
All I am doing is setting up a Data Validation list of say 10 items then in another cell I am trying to use VLookup to pull the corresponding information required for the data selected in the list selection cell but I keep getting "N/A"?
Am I right in thinking that you can't VLookup from a Data Validation list? If not how do I get round this?
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 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.
I am trying to create an interactive Price List / Quote Form. I have 1 tab (price list) that contains all data arrays. I have 1 tab (Items) that correctly calls avalable quantities based on a validation list and then Vlookup populates the formulas with the correct pricing & notes based on the quantity. I would like on the cover/quote page to have a drop down (in cells B23-30) where someone can choose a product based on the list, and then have the collums C,D & F populate with the rest of th information:
Column C with quantities for that product Column D with pricing based on that quanity Column F with notes for that quantity
Column E will calculate total based on simple math
Attached is example of what I'm trying to do. I want to use the drop-down on the "Master Tab" and have the corresponding price by promo_month appear in the yellow cells.
Currently its on July, but I would like to switch that to August etc and have new prices populate in the yellow cells. Prices come from the "table" tab.
I have a Vlookup which I want to modify so that it can become dynamic as the table array part of the vlookup will change.
So the basic vlookup is as follows: =VLOOKUP($R$3,ATTRIBUTION_FACTSET!$M$60:$P$73,2,0) but the data I am looking for wont always be in the range M60:P73.
So I tried to make it dynamic by doing the following: =VLOOKUP($R$3,INDIRECT("ATTRIBUTION_FACTSET"&"!M"&U1&":P"&V1),2,FALSE) The idea being that U1 and V1 would be numbers that can change so in this case U1 would equal 60 and V1 would equal 73
This vlookup is giving me #N/A and no matter how I modify it I cannot get it to work.
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.
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 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?
I 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
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?
