Dependent Dropdown Lists And Conditional Formatting?
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).
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?
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.
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.
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.
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.
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.
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".
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.
I'm using Excel 2007 and am trying to set up 3 columns of drop-down lists each dependent on the choice selected in the previous column's selection. It seems like it would be easy to figure out but I can't seem to do it.
I've already set up the arrays for the lists and "named" them but can't get the cell to be formatted automatically according to the previous columns selection.
How to colour cells depending on text in other cells, for example,
I would like cells D26:AA26 to turn light red if the letters 'FSM' are in cell 'E26' even if other text appears in it, eg 'SA+/FSM' I have been playing about with conditional formatting but can seem to solve the mixed text issue?
I am creating a heat map using two variables from a questionnaire: firstly the number of people impacted by a question; and secondly, the level of the impact. Respondents are asked to give these values in two adjacent cells. These are then multiplied to give a weight eg 1000 people x level 3 impact = 3000. I have used Conditional Formatting to colour the cell according to the impact level (0 = none - white (default), 1 = moderate - tan, 2 = significant - orange, 3 = high - red). What I want to do is then change the font size to give an indication of the number of people impacted, eg 1 - 1000 font 8, 1001 - 3000 font 12, 3001 - 8000 font 16, 8000 - 2000 font 24 etc. The size of font and colour will then represent the number of respondents and their impact.
I can't use Conditional Formatting as the value of 3000 could be derived from 1000 people at level 3 (red), or 3000 people at level 1 (tan). Is there another way of setting the font size depending on the cell value, eg by VBA?
In the drop-down menu in col D I select the value “Egress”, then the drop-down list in col E is populated with the values of EgressCol. Now I select a value, lets say “Cash payment”. If at a later time I change the value of col D, lets say to “Ingress”, then “Cash payment” in col E doesn’t match the values available for IngressCol (the correspondent dynamic list for the Ingress value).
I need a conditional formatting formula that checks whether the value in col E is listed in the corresponding list of the value selected in col D. Request: I found this formula that works fine, but it gets Excel to work really slow.
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.
I'm running Windows XP with Excel 2010. I would like to be able to have a group of 19 boxes of which each box has a unique entry, ie 1, 3B, 5C etc. Each box I'd like to have a way in which when selected only the single or dual unique characters show as a color. See attached spreadsheet with sample. When a cell with lets say 1 is selected, a drop box appears and the color and description are shown. Select a color but only show the 1 and the color selected, leaving behind the description. How can I do this for all 19 boxes. This seems like its a classic conditional format. Unfortunately it appears to be several orders of difficulty than a normal conditional format.
In one column I have a drop down list of a dozen different initials with conditional formatting on those cells. I would also like to conditionally format an adjacent text string column based on those initials, but don't want to set up a dozen rules on each cell, one by one. If I set up the top cell with the correct rules I cannot fill the formatting in because the formula is not updated on a row by row basis, but en bloc for the range.
I am building a spreadsheet with a column having two drop down menus at the top that designate independent, but strict conditions. For simplicity, say I have in cell A1 a drop down menu with choices "alpha, beta, gamma" while A2 has a drop down menu for "1, 2, 3". These six values sit off to the side in cells X1, X2, X3, X4, X5 and X6 respectively.
In cell A3 I woul like to change its color, perhaps to green because it is an acceptable number (say less than or equal to 100) or change to red if unacceptable, value greater than 100.
I can do both of these commands in Excel, no problem. In my situation, I would like to change the value at which cell A3 goes from green to red dependent on the options in A1 & A2. There are 9 such combinations of alpha, beta, gamma, paired with 1,2,3 and I would like the combinations to change the fill color of cell A3 in the following manner:
If A1 = alpha and A2 = 1, then A3 will be green for values less than or equal to 100 and red for values greater than 100. If A1 = beta and A2 = 1, then A3 will be green for values less than or equal to 80 and red for values greater than 80. If A1 = gamma and A2 = 1, then A3 will be green for values less than or equal to 60 and red for values greater than 60. If A1 = alpha and A2 = 2, then A3 will be green for values less than or equal to 95 and red for values greater than 95. If A1 = beta and A2 = 2, then A3 will be green for values less than or equal to 75 and red for values greater than 75. If A1 = gamma and A2 = 2, then A3 will be green for values less than or equal to 55 and red for values greater than 55. If A1 = alpha and A2 = 3, then A3 will be green for values less than or equal to 92 and red for values greater than 92. If A1 = beta and A2 = 3, then A3 will be green for values less than or equal to 72 and red for values greater than 72. If A1 = gamma and A2 = 3, then A3 will be green for values less than or equal to 52 and red for values greater than 52.
How to create a cell that can change its conditional formatting based on the selected values of two drop down menus.
I have a few other conditions I would like to implement, say change the fill color of A3 to green, orange or red dependant on the values in A1 & A2, but I might be able to crack that if I can see how the above scenario is done.
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?
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 ....
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.
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.
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!
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.
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.
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.
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!
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.
