3 Dependent Dropdown Lists / Combined Selection Provides Specific Information
Nov 11, 2013
I'm trying to create a database where when 3 dependent drop down list selections are combined, information is displayed.
I've tried VLOOKUP and INDEX/MATCH but to no avail.
Attached is the spreadsheet and what I am trying to accomplish. On the first sheet ("budget form") is a table named "Event Costs - room hire". I have created three drop down lists that interact based on what you select. Some rooms offer different services and so are dependent on the selection.
However, I want the final box to display the price depending on what is selected in the previous 3 lists. The amounts and a table I have created is in the "Product Database" sheet and the table is labelled, "Room Hire Control C".
View 5 Replies
ADVERTISEMENT
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
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
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
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
Feb 26, 2013
I am working on a spreadsheet for my Building company. I'm building a tool to price for Fascia, Soffits and Cladding.
I'm using Macbook Pro Microsoft Office for Mac 2011
Please see attached file
The first sheet holds all the lists of products, Category Headings list is in column A and then all the relevant products and prices are then from B to BI. I have defined each category with a name by selecting the cells and entering a name in the name box.
The second sheet is a Calculator in which I would like a to have a drop down list in the Category Column (Which I have worked out how to do myself - good old google) and then a drop down list in the second column which lets the user select from a list of results based on the selection from the previous column.
Example:
Category(B3) - 18mm Fascia/Replacement Board (Square White) - Drop down menu taken from sheet 1 A3:A33
Description (C3) - Drop down list containing all the options from D2:D15 Named "FasciaReplacementBoard18mmWhite"
So basically, whatever the user selects in Column B (from the category list) a drop down list would be available in Column C
The Value column would then show a value based on the options selected.
View 14 Replies
View Related
Feb 6, 2014
I have a dynamic dropdown list in B3. The dynamic dropdown list in B4 is a result of my choice in B3, and my dynamic dropdown list B5 is a result of my choice in B4.
When changing a value in B3 the dropdown lists B4 and B5 will be reset. This is done by this VBA Script in my worksheet.
View 1 Replies
View Related
Apr 19, 2013
I have data that resembles (I have also attached an example workbook as well if that is easier)
Column A.................Column B................Column C..................Column D.............Column E................Column F
Year.......................Quarter...................Month..................Product-Code...........[data A]................[data B]
2013.........................Q1.........................Jan........................SD-21...................13%......................0.05%
2013.........................Q2..........................Apr........................NV-12...................7%.......................6%
etc
I am hoping to have, on another sheet, a series of dependent drop boxes- Year, Quarter, Month, SKU- which after selecting then displays 'dataA' & 'dataB'.
So you could select- List 1: 2013 ----> List 2: Q1 ----> List 3: Jan ----> SD-21.................and then 13% and 0.05% are displayed.
The data will be continuously added to and so needs to be based on a dynamic data range.
I know that a pivot table is the perfect way to do this, however my bosses have requested that I do not use that format so it is easy for all staff to access.
So I think I need to construct some sort of dashboard sheet, I have experimented with OFFSET from other posts, but so far have had no joy.
View 14 Replies
View Related
Jun 1, 2011
What I am looking to do is this... (using Excel 2007)
I need to have more dependent lists based on the selection in Column A. (My column B is already set up and works perfectly using INDIRECT)
For example:
Column F would be a dependent list of colors based on the sales rep chosen in Column A
Column I would be a dependent list of managers based on the sales rep chosen in Column A
Column M would be a dependent list of part numbers based on the rep chosen in column A
without using VBA? (If I HAD to - okay, the other person maintaining the sheet would be lost....) I haven't been able to find anything close to what I want to do online - maybe I am just not using the correct terms.
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
Feb 15, 2014
I want to populate the value of a specific cell based on the choices made from two other dependent validation lists.
I am attaching a workbook that explains what I need to do.
View 9 Replies
View Related
Jan 29, 2013
I would like to Use two drop down list with two set of information to automatically update fields.
I have Job Titles (1-6) and Step (1-5). Each Job Title has a new pay rate and each step is an increase in pay. What I am trying to do is set up a drop down list where some one can drop down Job Title and step and have the rate automatically fill in.
I am able to get the Job title to auto fill the rate field, but am having trouble incorporating the step into the formula.
Here is what i currently have:
=VLOOKUP(H6,'Pay Rate'!$B$2:$G$12,3,FALSE)
H6 = Job tilte field on 'WorkSheet' (Sheet1)
'Pay Rate'!$B$2:$G$12 = Sheet2 and Step increases per Job Title
3 = the current column I was working with in order to make the rate appear
False = for exact info
I do not want to combine the two columns but keep them separate drop down list.
View 4 Replies
View Related
Dec 4, 2013
I have scoured the net for the following and have not been able to find a solution. I have found variations but not something specific for the use needed here.
I have attached a file with sample data which has the following format.
Column 1 = vehicle model (in this example golf, jetta, but there will be over 100 choices)
Column 2 = vehicle package option for specified vehicle model (automatic transmission, manual transmission, automatic transmission with air conditioning, etc...)
Column 3 = vehicle colour available for vehicle package option
Please note that Column 2 values for 'golf' are different than values available for 'jetta' (in the sample data I have blocked out common values with the same colour for quick and easy identification)
Also please note that Column 3 values of colour options vary for each 'vehicle package option'.
The behaviour that would be ideal is to have 3 drop down menus. The first drop down menu will allow selection from column 1 and will show each model only one time and repeated values will not be shown. The second drop down menu will only show the options available based on the selection in the first drop down menu. Likewise, the third drop down menu will show the colour options available based on the selection in the second drop down menu.
Since my data will involve hundreds of unique values in column 1 with plenty more added over time, it seems that the format of the data and the way it is laid out in the attached sample is the easiest way to organize it. It is perhaps also the easiest way to include new data without a lot of reprogramming.
Sample Data - Dependent Drop Down Cells Question.xlsx
View 8 Replies
View Related
May 21, 2014
I'm wondering how I could display information based on the user input through dropdown selection. I know how to make a dropdown-list already.
Example: A1 has a dropdownlist with Apple and Banana in it. If I select banana then I want B1 to display "8" and if I select Banana I want B1 to display "4".
View 5 Replies
View Related
Feb 9, 2012
If I make a selection using a drop down, can I have it auto populate another cell with information associated only to the made selection? For instance, if I select January from drop down in A1, I want it to auto populate B1 with 100.
View 2 Replies
View Related
May 31, 2012
I have a workbook with two sheets. The idea behind the workbook is an Interview Guide to be used just before an Interview. For now my problem is this.
Sheet 2 "Competencies" is just data. It stores competencies with their associated definitions and questions.
Sheet 1 "Control Page" is the sheet where the questions will eventually go. The user (Interviewer) will input data on the first two pages which will include name of candidate, date of interview etc. but they will also select 5 Competencies from the already existing drop down menus on page two. From there as the selections are made I wish for a code to copy the corresponding definition on sheet 'Competencies" to cells lower down in sheet "Control page".
I don't see a place to upload a file as I have a sample of the sheet ready to go.
View 2 Replies
View Related
Jan 29, 2014
I have filled up my ComboBox1 with the sheets I want to add information to. My problem now is how to place the info in the correct sheet when I click CommandButton1.
I've been working on this for a few hours now. I got this to work on the first code I found but it didn't work afterward. I just deleted the whole thing and started again.
View 1 Replies
View Related
Aug 21, 2014
Is there a simple way via VBA to alter the layout of data from a mixed up two column list into multiple headed lists on another worksheet (within the same workbook)?
The attachment should better demonstrate what I mean. Sheet1 has example data of how it is and Sheet2 shows how I would like it.
The data will be dynamic in the sense the numbers of unique values in column A will change (only increase, never decrease), as will the number of unique values in column B.
View 2 Replies
View Related
Nov 4, 2009
I am doing an export of info from one system and running an excel spreadsheet to manipulate the data. The problem is..That the exported data sometimes will export info into combined cells. Look at attachment
This is what I need...
I need the ability to search all the info on the POC tab and pull it into the info tab, by searching on a system and returning the POC info..
I have tried the vslookup formula and it will return the first row not the second row which is where the info is that I need (shown Highlighted)
=VLOOKUP(A4,POC!A2:b50,2,FALSE)
View 14 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
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
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
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
