Duplicate - Dependent / Related / Interconnected Data Validation (Dropdown Lists)
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
ADVERTISEMENT
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
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 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
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
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 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
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
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
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
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
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
View Related
Sep 26, 2013
I'd like to create a drop down list in data validation from a column of data that contains numerous duplicates.
For example, let's say column A contained hundreds of transactions with either North, South, East and West, how could I create a drop down list in another cell that only had four selection options?
View 5 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
Feb 24, 2014
I need to create three drop down lists, with the second dependent on the choices in the first and the third dependent on the choices in the second. This is proving to be quite difficult and takes a lot of processing the way I'm going about it (most likely the wrong way).
My data looks like this:
A
B
C
Macintosh Apple
Fruit
Apple
[code]......
The data is presented to me with the most narrowly defined column first, then the most general, then more defined.
What I need is the user to select (on a separate worksheet from the data) the category from column B, then the type from column C, then the specific item from column A. Each selection is dependent on the earlier selection, so that choice 1 (column B) determines the options for choice 2 (column C), which determines the options for choice 3 (Column A).
Also, I think I found that "VLOOKUP" has a problem where it is unable to match data from the first column based on data in a second or third column. For instance, in the above example, if I asked VLOOKUP to look for "Apple" and give me values in column A that contain "Apple" in column C, all I get is "#N/A".
View 6 Replies
View Related
Jul 22, 2014
I have a dashboard with 2 validation dropdown lists - 1 for Brand name (text) and one for month (mmm-yy) which go and run various pivots, etc.This works perfectly - except the dropdown list is so small its unreadable.
So I've tried a workaround using Combo boxes which I found here:
This does work, but when I select a month on the 2nd Combo Box, it is then displayed in number format eg, 41699. I've tried changing the format to mmm-yy but it keeps displaying as a number.
Is there any way I can make it display as mmm-yy?
View 14 Replies
View Related
Sep 17, 2012
I have been working on a template using validation tool so as to create drop down lists. Now I have to select data from a combination of two fields.
Example: I need info from Dataset A and Dataset B to create a new drop down list from Dataset C.
I tried with If AND formula =IF(AND(B4="Test";B6="de10");"ok";"no") where due to the selected values I got Ok as a return answer. However I need to have a dropdown list of relevant values selected and I am having some issues.
When I name my group (Dataset C) as DE10 so that it can be linked to DE10 value (dataset B) so that I can do a validation then I get an error as DE10 is an excel field name; therefore I thought of the IF AND solution as I was thinking of doing so that I can name Dataset C as XYZ (instead of DE10) on the data sheet however I am having some trouble in getting the drop down list to work.
Basically I need a drop down list from dataset C when certain info is picked from Dataset A and Dataset B.
View 1 Replies
View Related
Mar 28, 2012
I am currently working on spreadsheet which is using a mix of Conditional Formatting, Validation and a bit of VBA code.
The spreadsheet is a log of vacancies and applicants, When "vacancy" is selected in A I have introduced some VBA code to lock down cells S:AT on the same row.
The code is working but the issue is it is stopping you using the drop down validation throughout the sheet, if you click the arrow that appears on the cell nothing happens, However you can still access the dropdown by right clicking and choosing the 'Pick from Drop-Down List' Menu Item.
The validation is set up by ranges from another Worksheet.
The Code I am using is:
Private Sub Worksheet_SelectionChange1(ByVal Target As Range)
Dim myrow
Dim StarLock, EnLock
[Code].....
View 2 Replies
View Related
Feb 9, 2012
How to get rid of duplicate values in validation dropdown menu. I am using a range for the list that has a lot of repetitive values. I want to have only unique ones in the list.
View 9 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
Dec 2, 2013
Trying to set up a dynamic data validation, where each of the subsequent data validation boxes change based on the selection in the first data validation box (while maintaining 4 parameters within a cell, except for the last one).
I have used the IF-statements to determine the "Week's SUM" in H5.
What I'd like to do next is to determine a formula in the "Week's SUM" cells (H6,H7,H8) based on the choices in data validation boxes "Week #" (G6,G7,G8) with the following rules in place:
1. If I made a choice in G5 to be "1" which caused data validation in cell G6 to update, then in G6 I choose "2", the output in H6 should be just week 2's value of 20 (not the sum of week 1 and 2).
2. However, if I choose "3" in G6 (after I chose "1" in G5, as above) then I'd like the output in H6 to be the SUM of week's 2 AND 3, if that makes sense and so on. (e.g. If I chose "5" in G6, then output in H6 should be the SUM of week's 2 AND 3 AND 4 AND 5).
Same rules apply for H7 and H8. I have a feeling this may involve MATCH function and INDIRECT or SUMIFS but how to approach this.
I've attached a file : Dynamic DV and Dynamic SUMS.xlsx‎
View 3 Replies
View Related
Mar 11, 2013
I am trying to pull data from more than one drop down but don't the same data to show if already use, example as follow:
First drop down Contains:- Pants
Shirts
Hats
Shoes
Dresses
if I chose Hats it should not show up in the second drop down
second drop down Contains:- Pants
Shirts
Shoes
Dresses
Is this done in data validation or combo Box? if so how?
View 2 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
Aug 14, 2014
Using columns, (A,B,C & D)Column "A" is used for main grouping (defined as "zone") which I've used conditional formatting to define that there's duplicates. However the subsequent columns B&C are to and from devices. There may be instances of inverse duplicates. How can I either use "If then" formulas or LOOKUP function to note these occurrences? Column "D" is merely the flag column to display the instances of occurrence.
EXAMPLE
ZONE DEVICE TARGET
138 [TRF] 1-2 SCR 1-2 G 3287XT12G 51GT12G
138 [TRF] 1-2 SCR 1-2 G 351GT12G 287XT12G
138 [TRF] 1-2 SCR 1-2 G 3287XT12G 51LT12G PH 2
138 [TRF] 1-2 SCR 1-2 G 3287XT12G 51LT12G PH 3
138 [TRF] 1-2 SCR 1-2 G 3367 12G DTT287XT12G
View 1 Replies
View Related
