Multi-level Dependent Lists
Feb 25, 2010
I need to set up an excel sheet with dependent data validation that is multiple levels deep.
ie. Select value from dropdown list in Col A returns dependant list in Col B, select value from the list in Col B returns list in Col C etc.
Is this possible?
Also, is it possible to set multiple values to return the same list? ie in the drop down you have 4 items. Each returns a dependent list but items 1 and 3 both return the same dependent list. Just saves me making many copies of the same list with different names which isn't a huge issue.
View 9 Replies
ADVERTISEMENT
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
May 15, 2008
I have a 2-column employee file. Column A has the employee id and column B has the manager's employee ID. The reporting layers can get pretty deep. In this example, there are 6 layers of reporting. For example, Employee id 1200 (which is not in column A) has 2 employees. One of them (1712) has several employees, one of whom (1680) has employees reporting to them and so on for several levels.
Is there a way in Excel (2003 or 2007) to summarize the reporting structure? For instance, I need to know all of the employees that report up through emp id 1712 - not just that report directly to 1712, but all those under 1712's tree.
View 9 Replies
View Related
Apr 19, 2012
I have the following function. the problem with this excel is i give it "C:" as directory, it search only one level of sub folders i.e it will search the filename in
C: est
C: est3
It does not go deeper. i.e it wont look in folder that is for example
C: estanotherlevel
C: est3anotherlevelmylevel3
How to modify my function so that it goes to the deepest folder. is there a way to do it?
Function GetFile(directory As String, filename As String)
Dim FSO As Object
Dim fldr As Object
Dim subfldr As Object
Dim file As Object
Dim fullname As String
[code].....
View 2 Replies
View Related
Oct 24, 2007
I'm looking for a formula to pull the correct piece of data from one worksheet and display it in another. I have tried VLOOKUP, but it is only returning values from the first instance of my lookup value. Below I have included a sample of what the data from the first sheet looks like. My lookup value is entered on Sheet2 in cell A1. The formula I have used is as follows...
=VLOOKUP(A1,Sheet1!A2:D13,4,FALSE)
The result this would return for a value of 111111111 is 15, but I'm looking for one more level of depth in this lookup, by being able to pull not necessarily the cost of the first code, but of a specific code......
View 3 Replies
View Related
Nov 19, 2009
Been trying to figure out whats wrong with my formula. The 4th column doesn't seem to produce the intended result. I have attached here for reference.
View 4 Replies
View Related
Feb 9, 2010
create a multi level BOM in excel:
i have a formula
A=a+b+c+B
B=a+d+e
if i select A, i need excel to give 2a+b+c+d+e (and that should be in another sheet.
also i may take 50% of A +50% of B the resulting formula must appear.
i attached an exemple file.
View 14 Replies
View Related
Mar 13, 2014
So our company gets Material Statements which basically state what chemicals and how much of said chemicals go into producing X item.
I need to figure out the percentage of MASS for each chemical.
So data might look like this:
Total Weight of product: 500 grams or mg, etc.
Sub-Item - Contact - 450 g
Material - Copper - 425 g
Substance - Nickel - .05 g
Substance - Copper - 422 g
Substance - Lead - .02 g
Material - PVC Insul - 5 g
Substance - Polysomethings - .5 g
Substance - Red Dye - .4 g
Substance - Plastic - 4.1 g
So basically the sub-item line is 450/500
first material line is 425/450
substance is .05/425
the substance lines use vlookup to find the material line to divide by. formula i'm using is below:
=IF(B2="Sub-Item",I2/$M$2,IF(B2="Material",I2/VLOOKUP("Sub-Item",$B$2:I2,8,FALSE),IF(B2="Substance",I2/VLOOKUP("Material",$B$2:I2,8,FALSE),"n/A")))
B2 is either sub-item/material/substance
Col I is the mass of that line
M2 is the total weight of the item
Formula works until you get to the Polysomething lines in the above example data. instead of taking the material weight from PVC Insul, it takes it from Copper.
View 1 Replies
View Related
Dec 23, 2008
I am looking for some assistance to make pivot tables with multiple data values against a control item. A sample excel file is attached herein with. I am quite new to pivot tables was unable to find out a solution self.
View 2 Replies
View Related
Jul 24, 2009
Below is the partial copied text of a Multi Level BOM. I am trying to hide or unhide the rows with 4, 5, 6, 7, 8 and greater number of dots. The dots indicate the level of BOM.
Would a macro make the task more automated?
View 9 Replies
View Related
Feb 16, 2012
I am having a problem formatting a chart in Excel 2010. My chart has multi-level category axis labels, and I would like to have a vertical grid line separating each major group of categories. In Excel 2003, I could right-click on one of the gridlines and then specify the spacing I wanted between gridlines. In Excel 2010, as soon as I indicate that I want multi-level category axis labels, I get a vertical gridline between each category and I am unable to alter the spacing. If I deselect the multi-level axis label option, I can adjust the spacing between the vertical gridlines, but the axis multi-level label functionality is lost. Is there a way to fix this problem without having to resort to using the drawing tools or text boxes to achieve the desired results.
View 3 Replies
View Related
Jun 27, 2005
I've created a dependent list in Excel using Data validation. The first list is independent and the second list depends on the first list. I'm facing one problem in this. After selecting a element in first list and corresponding element in second list, if I want to change the first list again, I can go and change the value. But the value in the second list remains the same. I want the second list to be empty when I' selecting the first list again. How can I do this?
View 14 Replies
View Related
Jul 6, 2009
I have been searching on this for a little while now but have not been able to find a solution to my problem. I have been asked to create a series of dependent dropdown lists using excel so that we can use these for inventory imports into a asset system. The catch on this is that nobody here really has any idea on how to make these dependent lists reference each other correctly. The reason for that is because the fields all have spaces in them and I do not know how to get a list name with a space in it or to create the proper translation for it. An example of this would be something like what I have written in below ....
View 13 Replies
View Related
Dec 28, 2006
I have been able to use the previous information to create dependent drop down lists but I have been unable to apply the settings to entire columns versus just one cell.
Is there a way to get the dependent lists to correspond to the cell beside it without having to manually type in each cell name?
View 9 Replies
View Related
Jul 29, 2006
how to do this and I just can't seem to figure it out...I've come close, but something always seems to be incorrect when I try to do it.
So what I have is 5 column sheet (please see the attached file).
What I would like to do is have the 1st column (Main Folder) be a drop down list and have the other 4 columns dependent on the 1st.
So for example, if I choose Business Hierarchy then in the Subfolder, Subfolder2 there should be nothing shown and under the Attribute column, there should be:
ALL
BUSINESS SEGMENT
REGION
AREA
OPERATING MARKET
COMPANY
MASTER COMMUNITY
COMMUNITY
shown to the user.
Or if I were to pick Unit from the Main Folder, then in Subfolder Unit, Unit Options, Unit Budgets, Dates, etc. should be shown while in Subfolder2 the items for Corresponding to Dates should be shown.
Currently the description box is blank, but will be needed to correspond just with the pertinent main folder selection as well.
Please let me know if you guys have any suggestions, I greatly appreciate. I'm still learning how to do this kind of stuff, so please forgive me again for having trouble with this.
I also understand that if I were to cluster the information in the Attributes column into single cells such as:
ALL
BUSINESS SEGMENT
REGION
AREA
OPERATING MARKET
COMPANY
MASTER COMMUNITY
COMMUNITY
into one cell, then it is very simple, but I do need the seperation between cells due to the description column.
View 9 Replies
View Related
May 20, 2014
I'm trying to simplify a template we use regularly at work. On the front end page, the one that eventually gets turned into a PDF and sent to a customer, I have a selection of drop downs, which I am pretty comfortable with. I also have some other fields that are populated by vlookups, and again, I have no problem there. What I am struggling with is creating a second drop down list that only has options relative to the first drop-down list. From what I gathered by searching on this, I may have to change the format my data is in, note that I have a pretty basic knowledge of excel.
Master Data (from where I want all the drop downs to come from) - see attached screenshot.
Capture.JPG
So, on front end, I have a drop down that selects from column A. Beneath that there are two rows that auto-populate depending on what company was selected in the drop-down.
What I want is a second drop down list from D, E & F.
So, If I select ABC Limited in the drop down menu, the second drop down should only have John, Jim & Jane as options.
Is it possible to do this with the data in the format I have, or would I need to change the format? I have +/- 500 entries to do, so obviously I'd like to not change my format if possible.
View 5 Replies
View Related
Dec 4, 2013
I have been having alot of trouble and have read alot. I can't seem to figure out the problem. I am currently working on an excel sheet that will select a name from the first drop down list. Based on that name five other list will be generated just on the first selection.
View 3 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
Jul 30, 2009
I'm trying to make a number of cells dependent to a drop down list. However, these cells would also be drop down lists and while they'd show a value automatically determined depending on the drop down list, the user would still be able to choose another value.
Aluminium
Coated steel
SS304
SS316
This would be my initial drop down list.
In the same sheet, I could choose the materials for the machines shaft, fasteners, anchors, chains, sprockets, nozzles, etc. These materials would be, again:
Aluminium
Coated steel
SS304
SS316
in 90% of the cases, having the machines frame at SS304 would mean the rest of the materials would also be SS304. However, depending on the needs, the anchors could be SS316. So basically I'd like that the options all be in the same material as the frame, but with the possibility of choosing another material through a drop down list.
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
Mar 20, 2009
I have attached a file where I’ve made some dependent validation lists. When the first box is changes it clears the last 2 with this bit of code
View 2 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
Sep 23, 2011
I am trying to get a listbox I created in from DataValidation to be dependent on another list.
For example, when I select Course 1 (a list item) from the Course List drop-down, I want the cost to auotmatically populate in the Cost drop-down (the other list).
How do I get this to happen? Here is the code I created in VB, and I am not sure if this is the right course of action:
Sub Automated()
Sheet1.Cells(2, 1) = "MATLAB"
If (Sheet1.Cells(2, 1) = "MATLAB") Then
Sheet1.Cells(2, 3) = "31"
End If
Sheet1.Cells(2, 1) = "INCA"
If (Sheet1.Cells(2, 1) = "INCA") Then
Sheet1.Cells(2, 3) = "41"
End If
[code]....
I don't want to use the user form, however from VB. I want to use the regular drop-down. The code seems to work, but, each list item in the drop-down is not pulling the data I want it to. I need to activate the drop-down as a click event somehow. It's just recognizing the cell A2 as a whole and not the individual list items. I want to each list item to be their own object, and to automatically populate the cell C2 with their cost when they are selected.
View 3 Replies
View Related
Feb 21, 2012
How to do a third dependent list with the last two list depending on the first. The con textures site does not explain this.
View 1 Replies
View Related
Jun 22, 2009
I have a 2-part question:
1) How can I create 2 lists dependent on the same primary list?
2) Once I get number1 resolved, I have a data sheet of 30 col and over 6k rows. I need to create a report that when a user select the 3 options from the dropdown lists (Category, Location, Product) the Report sheet will get populated with data from my data sheet based on those selections. I'm looking to have somewhere between 10 and 13 columns (numerical, general, text data type). Is there a way to automate this process vs. writing 10s of lookup formulas? to bring the data in? My workbook is already over 25MB!
View 9 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
Oct 4, 2006
Is there any way to make Hack#24 work with more than 5 lists? I followed the information in the book and was able to get 5 secondary cells to have a drop down list that was dependent on the primary cell selection (also from a drop down list). But when I tried to apply this to the next six primary and secondary cells it would not work.
View 5 Replies
View Related
Jan 3, 2008
i need a way so they can be differentiated. This is how my validation are set out
and my details of cars { list}
http://img509.imageshack.us/my.php?image=oysc7.jpg
once the end user chooses the model i need it to display the information of that model like the Price for a day and NO of doors
View 9 Replies
View Related
Jun 14, 2013
I have three drop-down boxes in 3 adjacent columns. Column 1 is free-choice, Columns 2 and 3 drop-downs are variable dependent on what is in Column 1. That works fine.
I have an issue with over-type but I can solve that with protection. Again fine.
BUT:
1. How do I make the user choose something i.e. not just leave the cell in column 2 or 3 blank by ignoring it (i.e. blank is an error but only after drop-down in column 1 is activated)
2. Also, if the user has completed the line (columns 1,2 and 3) and then changes column 1, columns 2 and 3 are now reading from the incorrect drop-down boxes (i.e. they are now in error but this is accepted and not flagged).
View 2 Replies
View Related
Aug 11, 2009
I have worked on the excel sheet Test.xls(Attached) to create a simple uniform order form. Unfortunately, I am not able to get through the 3rd column. Let me explain. 3 Columns references below 3 steps
1. I selected the first dropdown item and selected the item
2. Corresponding value is shown based on the first choice.
3. This value or drop-down is not shown.
In my main "LISTS" sheet, I have items that include one of the item for example... Blue Socks & Shoes with characters "&" and spaces.
All this could be solved if I maintain the following idea.
1. Have the main list without spaces.
2. The corresponding heading is related to the items in the main list (without spaces).
3. Enter the Headings for the sublists and sub-sub lists without any spaces.
Great..I am getting lost with this..Can anyone help me.
You will get more clearer once you see the attachment.
View 10 Replies
View Related
