Removing Multiple Invalid Characters For Dependent Data Validation List
Oct 14, 2009
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.
View 3 Replies
ADVERTISEMENT
Jul 28, 2014
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
View 12 Replies
View Related
Aug 1, 2013
B2 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.
View 3 Replies
View Related
Jul 25, 2013
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.
View 2 Replies
View Related
Mar 20, 2008
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 Related
Oct 4, 2008
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 Related
Sep 12, 2007
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.
View 9 Replies
View Related
Apr 24, 2014
How I can Removing the Black cells in list in data validation ?
View 1 Replies
View Related
Mar 8, 2009
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.
View 2 Replies
View Related
Feb 29, 2008
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.
View 3 Replies
View Related
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.
View 3 Replies
View Related
Jun 12, 2014
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.
Attached File: dummy 1.xlsm
View 7 Replies
View Related
Dec 1, 2011
I just have a basic data validation list. I used "List" and checked the right boxes and the file has been working before. Now the same file does not stop the user from typing in anything.
I re-did the same data validation in a new file and it works. Was there something in the file that prevents it from working?
I have Excel 2007. I saved in both xlsx and xlsm formats
View 5 Replies
View Related
Dec 11, 2013
Currently I've been able to remove the prefixes and suffixes using the replace option as those were consistent across the data set, I'm now left with a series of numbers that look like this:
11.225D2
11.24c1
11.28.r1
11.31.1.2h1
11.36.g1
11.39 A2
11.39B1
11.3c1
In most cases I need to remove the information after the last period. Some of the data has a space at the end, instead of a period and then some have no space or period but have the letter that needs to be dropped. I'm using xl 2011 for MAC.
View 6 Replies
View Related
Apr 10, 2009
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.
View 3 Replies
View Related
Jan 16, 2014
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.
View 4 Replies
View Related
Oct 14, 2008
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!
View 4 Replies
View Related
Dec 31, 2012
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 Related
May 5, 2009
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.
View 10 Replies
View Related
Sep 10, 2009
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 Related
Dec 10, 2013
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
View 1 Replies
View Related
May 26, 2006
I 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.
View 9 Replies
View Related
Feb 25, 2007
I have an excel sheet with approx 30,000 rows of data.
These are all keyword phrases.
They can be related to any subject, but for this example these are related to the root keyword phrase of "car Rent"
I have some software which basically pulls in keyword phrases from search engines and meta tags etc, including misspelled keyword phrases.
My problem;
The data is uncleaned.
In other words there might be I think it's called "Carriage return" data in there, so the row of data might be very deep (Instead of a row height of say 10.5 it could be anything, IE some could be 100 or 200 even).
There are unwanted characters, for example; ()[]{}+?!""^*
(If it could delete all unwanted characters except for letters/digits)
There is a problem I see, that if it removes _ or - between words, that it will join the words together which won't be of any use. If it deletes anything with a letter either side of it or a letter and digit, or 2 digits, 1 either side it would then need to add a space to replace the hyphen.
So for example; if there was a phrase in the list like ....
View 9 Replies
View Related
Apr 10, 2014
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
View 4 Replies
View Related
Jul 24, 2014
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?
View 4 Replies
View Related
Apr 8, 2009
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: .....
View 14 Replies
View Related
Jan 27, 2014
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.
View 3 Replies
View Related
Aug 11, 2009
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.
View 4 Replies
View Related
Feb 4, 2014
I have created a DAta Validation List and it only select one item. I want it to select as many items that is on the list but i can't get it to work for my main worksheet I am working on. I can't figure out why I can't get it to work for the work sheet "This Don't" but it works for the worksheet "This Works" why and how I can change it so both Reason (Column F and H can) select mutliple items on there. Data Sheet.xlsx
View 2 Replies
View Related
Mar 25, 2014
Can you create a Pull-down List that contains data from Multiple Ranges.
If I have a List of Names Running down column A (A2:A10) another List of Names Running Down B (B2:B25) and a Third down C (C1:C15) each of them named Ranges ("List1", "List2" & "List3"), can I create a Pull-down list in Cell A1 that would include the names from all three ranges?
View 4 Replies
View Related
