Data Validation Lists: Tree Style
Oct 7, 2006
I have a data validation list in B31 "material type" that has 4 options (Adhesives, Metals, Backer Board, & Grout). Based on that selection F31 needs to display a list of "descriptions" that match that "material type". The list is in book "database.xls!sheet3". Material type list is Column A, Description is in Column E. I know that you can't link to an unopened workbook in data validation, & I've been trying to code it, but again the lack of expierence & knowledge has frustrated me again :P This code to get the info for the file to open
Private Function GetValue(path, file, sheet, ref)
Dim arg As String
If Right(path, 1) <> "" Then path = path & ""
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)
GetValue = ExecuteExcel4Macro(arg)
End Function....................
View 9 Replies
ADVERTISEMENT
Aug 1, 2006
I'm trying to write a macro that will build a tree from 3 sets of lists and I'm really stuck!.
The thing is:
1. I have 1 worksheet with 2 sheets. First sheet "Lists" contains in every column a list of data.
2. Range("B:C") contains list od SERVICES1 - every value in list has its corresponding ID_SERVICES.
3. Range("D:F") contains list od SERVICES2 - every value in list has its corresponding ID_SERVICES2 and a parent id ID_SERVICES.
4.Range("H:I") contains list od SERVICES3 - every value in list has its corresponding ID_SERVICES3 and a parent id ID_SERVICES2.
5. Second sheet "Project" in Range("A:C") contain example of tree like: ...
View 3 Replies
View Related
Sep 26, 2007
I would like to know if it's possible to populate a data validation list based on what is selected from 4 validation lists?
for example:
On sheet1:
If 'Group1' is selected from data validation list1 then data validation list5 will show a list of all items from Group1. If 'Group2' is selected from data validation list2, then data validation list5 will display all the items in 'Group2'...
(I do not want to use a combo box for this)
View 9 Replies
View Related
Sep 22, 2008
I have a very large spreadsheet that I work with on a daily basis. The main portion of the spreadsheet is broken down into weekly sections (for example, columns GF thru GM contain data sections for one week, which then repeats with the next set of columns, etc.). Below these weekly sections are detail and summary sections for all the data entered above. In the main weekly portion, each week contains a size drop-down list which calls to size information in the detail portion below. Recently, the data validation lists have started disappearing. By disappearing, I mean that the data validation for the cell no longer exists on a large portion of the list cells. I have tried recreating all of the data validations, saving the file, and re-opening it, and when I do, they've disappeared again. I have no idea what to do at this point. The functionality of the spreadsheet is crippled without the sizing drop-down lists (for various lookup reasons).
View 14 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
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
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
Aug 12, 2014
I have a question regarding linking the content for data validation lists stored in a different workbook.
The scenario is as follows: I will have say 50 users using a template which contains various dependent drop-down lists. These lists work by using the INDIRECT function which calls the correct drop-down based on the previous selection. The drop-down entries are currently stored in named ranges on a hidden worksheet in each user's file.
However, if I need to make a change to the content of these lists, I will have to open all 50 files individually and change each one. So I'd like to have a master file to which all the templates would be linked which would contain the entries for each drop-down. So when I update a list in the Master file, it would automatically update the drop-down lists in each user's template with any additions deletions.
My question is, how do I create the links between the user templates and the master and have it so that the list ranges will be dynamic. So if a drop-down list contains say 10 entries and I add 2 more in the master, that those 12 now appear in each user's template?
View 3 Replies
View Related
Aug 28, 2007
I have a question regarding the data validation lists. What i have is 4 different lists
Department
Assays
Instrument
Manufacturer
What I want to do is...once a user chooses a department, he gets a list of assays done in the chosen dept. and also a list of instrument used by the dept and a list of manufacturers
I used the examples shown here
http://www.contextures.com/xlDataVal13.html
http://www.contextures.com/xlDataVal02.html
and was able to succeed in creating dependent lists to some extent...I am attaching whatever I have able to accomplish so far, which is able to pull up the assay list based on the dept. ...but am confused as to how to get the Instrument and Manufacturer list at the same time .....please choose 'Chem 2" from the dept list to see what I have managed to do so far
View 11 Replies
View Related
Oct 22, 2007
I am creating a spreadsheet i want it to be that i make a selection from a drop down and this will then narrow the options available in the next drop down. Say box one you select 'half day' and then the next drop down menu will automatically loose all the data for a 'full day' and only show half day information such as costs for food and room bookings ect. i have made the drop downs but im not sure what to do next.
View 3 Replies
View Related
Oct 27, 2008
Sheet1 is my database and looks like the following:
HTML ABCDEFGHIJK
1RefNoSetSubsetStatus
2101lambsheepINP
3102catpetCOM
4103chickenbirdINP
5104milkcowINP
6105turkeybirdINP
7106honeybeeCAN
8107dogpetINP
9108boybabyINP
Sheet2 is for the user input, in which Columns H and I would have a data validation drop-down list and looks like the following: ...
View 14 Replies
View Related
Jan 4, 2014
I have three lists and I want to summarize them in one table. I can do this by formula or using Data validation by position. Who is the best way? There is a difference between them?
View 3 Replies
View Related
May 19, 2008
On sheet 1 I enter data such as:
Visit Date | Customer Name | Customer Number | Job Number | Product
On Sheet 2 i have a list of our 130 products which would be similar to '1130Tots Standard 24" ' I have made these as a list so we can choose from the list using data validation.
Now, out of the 130 products we have about 4 main different Catagories - Tots Direct, Tots RTS, Direct, RTS. - I could put what category they are in in a column next to the list on Sheet 2.
What i would like to happen is if e.g. a product is chosen from the drop down list, it could look in the cell in the column next to it and see what Catagory it is in and format the entire row on Sheet 1.
E.g. Direct changes colour to grey, Tots RTS to Pink, Tots Direct to Grey/Pink stripes, RTS stays standard no colour.
A bit of a development on a question i asked last week to cut out another stage. Currently people have to enter the product, then choose in the next column whether is is Direct, RTS, Tots Direct, Tots RTS then conditional formating will colour the rows. - Want to know if it is possible to do this all in 1 go by selecting the product from the list.
View 10 Replies
View Related
Jan 7, 2012
I have a need to use data validation for user selection of dependent lists, but dependent upon what the user picks from a previous list I need some selections to disappear or become hiden somhow.
For example, let's say I want to first pick from a list of Countries, then States, then Cities, but some of the Countries do not have States, just Cities and some have both. When a Country does not have any States I need the States data validation drop down box to disappear and the cell to become blank so that the user knows there is no selection needed.
I know how to make the dependent data validation lists, but I do not know how to make one of these drop down lists disappear based upon a previous selection in dependent lists.
View 4 Replies
View Related
Sep 28, 2007
I have on a sheet:
4 data validation lists that contain the exact same values.
would like to have:
One validation list box that is dependant on all 4 vd lists.
I posted a thread on dv and came to the conclusion that it is not possible to use INDIRECT() as it only allows for one cell reference and not four.
Is there another way to do this?
Glove Man, I tried your suggestion but couldn't get it work.
View 9 Replies
View Related
Sep 1, 2009
I'm trying to create a cell with multiple dropdown lists in E4 depending on a value from another drop down list in B4. Both E4 dropdown lists are named cell ranges RV_MECHANISM and VALVE_OPERATING_MECHANISM_TYPE. I know how to do this in Data Validation using an IF formula, however, that's not quite "bullet proof" or "idiot proof" enough.
I'm attempting to create a macro to load only the appropriate dropdown list in E4 as called for by B4.
I recorded the Data Validation dropdown lists using the macro recorder, then searched through all appropriate posts I could find at MrExcel and attempted to write the appropriate macro code as follows;
Sub RVorMECHTYPE()
With Selection.Validation
.Delete
.Add Type:=xlValidateList
If Range("$B$4").Value = "RV" Then
ValList.RV_MECHANISM
Else
ValList.VALVE_OPERATING_MECHANISM_TYPE
End With
Range("E4").Select
End If
End Sub
Compile error: End With without With
That's as close as I can get ... can one of you kind souls direct my macro coding paths and offer up the correct code for accomplishing this task.
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 13, 2014
On Sheet1 I'm attempting to auto populate specific cells in columns B,C & D with information found in Data Validation lists (found on Sheet2) based on the "value" chosen from a list in column A. For instance,
If A2=Pig Then B2=Slop, C2=Pen, D2=Food
Is this best accomplished through VBA or a basic Function?
View 5 Replies
View Related
Aug 17, 2009
I have a cell that I want to use data validation on so I have a drop down list. Problem is the location of this list will be in another workbook.. Is this possible to go from one workbook to another using data validation?
Also, depending on the information that is selected from the drop down list I want a cell to the left to pick the corresponding data from the list in the other workbook. These forms will always be in the same folder.. Not sure if that information is necessary but just in case you need to know.
View 4 Replies
View Related
Aug 31, 2006
I've got a table that has to be manipulated by multiple users. Within the table I have several column ranges with validation lists (in drop down boxes) in order to limit responses. However, I'm finding that most of the users are pasting data in from other sources that may not have been in the validation list, or may be in a different format, etc., and it essentially undermines the reason for using the validation.
I've tried protecting the sheets using various options and tried looking around online for a solution, but I'm not finding how to get around this.
View 9 Replies
View Related
Jan 9, 2014
My Table has a data validation list in one row of a table. And different formulas in different rows.
The Table is expanded only into columns. Which means, there will be no new rows...only columns will be added.
If we enter text into the header cell in a new column, the whole new column gets formatted, but the list and formulas do not auto fill into new columns.
Of course an easy way is to copy and paste a column...but is there a way to auto fill lists and formulas into columns of the table? Similar to how the rows get autofilled?
View 4 Replies
View Related
Jun 6, 2014
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.
View 2 Replies
View Related
Jun 20, 2014
I am having trouble delete a drop down list inspite of selecting clear all from the data validation tab in excel 2007. When I hit Alt + Down Arrow, I still see picklist options. I didnt set up the spreadsheet, hence I am not sure how to begin troubleshooting.
View 1 Replies
View Related
Jul 28, 2014
Is there a way to add cascading lists (from data validation or form/activex controls) to my excel spreadsheet WITHOUT using named ranges? Maybe structured references?
I need to avoid the named ranges because it will cause my workbook to have duplicate named range titles which I cannot avoid.
As a general example my issue arises because I have something like this where the titles are the same but they map to slightly different data. These also have to be cascading because the titles align to another list which I do not show in the example. I also considered using pivot tables, but the issue there is that the data validation lists repeat in the same worksheet. So I would have 3 cascading lists in row1 dependent on each other, but the same 3 lists in row2 dependent on row2 but not the previous row.
[Code] .....
View 3 Replies
View Related
Nov 10, 2008
I cant seem to find the correct syntax for creating 14 validation lists using array members as the source of the named ranged. The validation lists are stored on a different worksheet, the Named Ranges are created fine, as are the ranges that are having the validation applied. The Syntax I am having a problem with is
Public Sub assignDVList(WSD As Worksheet, sListName As String)
Dim DVListName As String
DVListName = "DV" & sListName
Application.Goto Reference:=sListName
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & DVListName
It is the Formula1:="=" & DVListName that is creating the headache. The sub is called as the array moves through the columns, using the header row as the Name for the Named Range, and the data Validation worksheet uses the same naming except it has DV in front.
View 4 Replies
View Related
Oct 26, 2012
Is there any way to modify the 1st style under the Light category? I like it because it's the most simple but it won't allow me to Modify or Duplicate. Is there a work around to duplicating/copying it? I've tried manually creating a new style but I can't get it to get the lines fills correctly.
View 2 Replies
View Related
Jul 28, 2009
I have an organizational hierarchy dump in excel from an SAP ERP system. The format of that file is less than user friendly and I need to convert it to a flat file format so I can do pivots against it or possibly dump it into access database. The background is as follows (I am also attaching a file).
The organizational structure is multinoded/layered. Top most node is a company second from the top are divisions designated by 02DivName each 02DivName has multiple nodes assigned to it. The lowest level of the hierarchy is a department, always a numeric, always same length.
That's where it gets interesting. Each node/layer under 02DivName has a technical description and text description they get broken up into two different columns when SAP dumps them. Technical descriptions can be used to identify where in hierarchy the node falls(parent/child). For example 02A is a child of 02DivName 02B is a child of 02A 02C is a child of 02B and of course all of them roll up to 02DivName. There could be multiple 02A's under Div Name and their respective 02B's, C's D's would roll under them. So on export file it would be graphically represented 02A1 blank blank blank blank in that column and then change to 02A2 their respective 02B's would be in different columns works the same for C's and D's.
Here is another wrinkle the department is not always assigned to the lowest level grouping. I could have 02DivName dept, dept, dept, and then 02A group assigned to 02DivName. The department numbers end up on export file in the same column as fre form text descriptions for the nodes. It is staggered format.
The object of what I'm trying to do is basically convert the gibberish described above into flat file format via a vba script. I need to have node1 node1 desc node2, node2 desc, etc etc in columns the last column being a department number. So if for example I have a dept assigned directly to 02DivName directly all the other node descriptions will be blank if it's node 02A then the node1 and node2 will be populated. Any help will be appreciated. And here is the file with excel spreadsheet
View 5 Replies
View Related
Jun 24, 2009
I have a question on the above but can't seem to find a solution. There are two ways that I can find for dealing with dynamic lists via data validation:-
1) Offset and match, cavet being the data must be sorted a-z
2) Have lists for each potential selection
Is there any way to get around 1 without having to do 2? E.g. Got two columns of data, unsorted, and a list from which the user can choose from. The user chooses from the list in first cell, in the second cell require the dynamic list to return all the values accordingly?
View 5 Replies
View Related
Mar 30, 2007
I need to find a solution that will prevent users from pasting values into cells that do not match values in a named range or validation list.
I think the best method would be to have it trigger with a SheetChange event. I would like it to check to see if the enteredpasted value(s) in a range of cells is equal to one of the following:
K
1
2
3
4
5
6
7
8
If it does not, I want the cell cleared and a message box shown.
I am sure this is simple, stock code for someone, but I am teaching myself vba and am fairly green. Does anyone have this handy in thier code library?
View 13 Replies
View Related
