UserForm Dependent Dropdown Menus Refresh?
May 5, 2014
I have put together a user form with two ComboBoxes, one dependent on the other. The first box has a drop down menu from which the user selects a name, and the second drop down populates a list from a named range according to the user's selection in the first box. The code I used is as follows:
[Code] .....
This has worked so far, however, if one selects a name in ComboBox1 and then changes their selection (ex. Initially selects John Smith from the first menu, and then changes their selection to Mike Roberts) there is an error. I am not sure why - I think the issue stems from the 'Clear' function but I am not sure how to resolve it.
View 2 Replies
ADVERTISEMENT
Mar 11, 2013
I need the drop down menu to update when the user adds a new entry. For example, if the user decides to add companies to the financial table, no matter how many new rows they add, our drop down menu at the top will update to include the new the entries. I want this to be the case in every table. How do I do this?
View 5 Replies
View Related
Jul 14, 2009
I'm a little out of my element(normally program PLC's). I have always been lucky on searching others programs and have been real successful at piecing them together to fit the application I needed. The current workbook I am working on for my supervisor will allow him to keep track of personnel's weekly job assignments. The workbook and userform work fine when just using one worksheet.
What i would like to be able to do is use the "assigned to" combobox be the indicator as to which worksheet the data is saved to. I have tried to do it several different ways but have been un-successful. Have spent the last two days reading and searching for a solution but have yet to find or understand one.
View 4 Replies
View Related
Dec 10, 2004
I am compiling questionnaires in Excel and need to constrain the answers. I've successfully used validate, list and directed it to a list (containing, for example, 'USB', 'Firewire', 'SCSI'. When I first set this up, the drop down menu appeared (and the prompt message I had created). After I copied the first worksheet to use as the base for my next questionnaire, the drop down menu disappeared, although the prompt still shows and the validation still works.
Have I missed something fundamental?
I've tried creating one on a brand new workbook and copying the worksheet and it seems to function correctly. Why has my drop down menu vanished???
View 9 Replies
View Related
Mar 9, 2013
I have a 'diet diary' that I have built in excel that has many of the common meals I eat and details calories, carbohydrates, fats, etc and I use it to keep track of what I have eaten in the day.
However, one of the features is that on the front sheet I have lots of drop down menus that I select the different things (example, milk, banana, spaghetti bolognese) which in that puts in the nutritional info. However, each day I have different numbers of items to put in so I have to change the slots that no longer have an item in back to the default value so that it doesn't add calories. What would be ideal is if I could highlight a bunch of boxes at once and then make them all select the 'no-item' option in the drop down box simultaneously rather than selecting them all individually.
View 3 Replies
View Related
Jul 18, 2013
What I would like to do is use a drop down list Type 1, Type 2, Type 3 etc and then be able to use whatever is selected to reference a value in another cell.
Excuse my ignorance but something like Cell 1 * (If Type 1 from the list is selected use figure in Cell 3, If Type 2 from the list is selected use figure in Cell 4..........) etc etc
View 2 Replies
View Related
Oct 19, 2012
I need to link this summary sheet projects to this plant and equipment list sheet to show each project numbers separate weekly cost hire on the summary sheet.
View 3 Replies
View Related
Jan 31, 2013
I am trying to add two drop down menus - part number and material description. I would like to be able to choose an item from either menu and have it populate the rest of the row with the item information.
I know how to create the drop down, but I don't know how to make it work so I can choose from either one of the two drop downs and have the other automatically populate.
View 1 Replies
View Related
Jun 25, 2014
Create a dynamic chart which is based on multiple dropdown menus. I'm trying to design a chart which shows figures per city. In the first dropdown menu you can choose a city.
The other two dropdown menus will determine the date range of the chart.
How to create this chart ....
View 2 Replies
View Related
Sep 29, 2011
I have a table in cells A1:B11. I have two drop down menus in cells G3 and H3. When I select red in G3 I need Bob, Sam and Sarah appear in the drop down menu in cell H3. Instead, with my current formula, only Sarah appears in the drop down in cell H3. When I select green in G3 I need Tommy, Bob, Sandy, Carl and Phil appear in the drop down menu in Cell H3. Instead with my current formula only Phil appears in the drop down in cell H3.
I linked cell H3 to the defined name Names which equals
OFFSET(INDEX(Sheet4!$B:$B, MATCH(Sheet4!$G$3, Sheet4!$A:$A,0),0),COUNTIF(Sheet4!$A:$A, Sheet4!$G$3)-1,0).
What should I add to this formula to make it work correctly?
ABCDEFGH
1RedBobRed2RedSamGreen3RedSarahBlueRedSarah4GreenTommy5GreenBob
6GreenSandy7GreenCarl8GreenPhil9BlueCarl10BlueSam11BluePhil
View 9 Replies
View Related
Oct 21, 2007
when I sort data columns that contain drop-down menus. My spreadsheet has several columns: "Patient name", "Room #", and "Phone #". Each cell within the "Phone #" column should have a drop-down validation list containing two possible entries: the room's designated telephone number, or NONE. Whenever I add a new patient to a room, these three columns need to be sorted alphabetically by patient name. But when I sort all three lists simultaneously, it looks great- but then I realize that the drop-down lists associated with each "Phone #" cell haven't sorted! They stay in their original location and don't sort with their cell. So the designated telephone numbers for each room are all screwed up.
View 9 Replies
View Related
Apr 1, 2009
i have a table of tools and their properties which i am using to provide validation lists for a number of other worksheets that record when those tools were last calibrated/tested and when they need to be calibrated/tested next.
many tools require more than one form of testing (electrical and/or calibration), but not all tools require both.
can i create some kind of drop-down menu the looks at the tool master list, but only gives the option of those that require the particular type of testing i'm recording on that particular list? how can i create a drop-down list that is more limited than the range i have selected for it to display?
View 10 Replies
View Related
Jun 11, 2013
I have a problem (I have intermediate knowledge of Excel): lets say that I have two model years for Cadillac: 2013 and 2014, and have 3 car models for year 2013 (ATS, XTS, and CRX) and two car models for year 2014 (ELR and CTS Sedan). I want to create a dropdown menu under "Model Year" that will show me 2013 and 2014. When I click on either one, a dropdown list for all car models for that year can appear in the next column under "Models". Then, for each car model, there are different characteristics that are in other columns, such as horsepower, mileage, etc...I want all of the content to stay on one line (row), but change when I click on a different model.
View 2 Replies
View Related
Nov 5, 2013
On the spreadsheet there is 31 days (a separate worksheet per day).
at the bottom of each sheet there is the case notes for that day. I would like all that information collating on the last sheet of the workbook (named Cases).
I would like to be able to sort through that data with the use of some drop down menus.
All i need to sort is the Brand and if the case is open/closed.
what to do so I can try myself or using the attached spreadsheet be able to sort this problem out. If you choose to use the spreadsheet to meet the requirements
View 14 Replies
View Related
Mar 16, 2014
I am looking to create an index array. the conditions for the array are based on drop down menus. I want to have it that if the drop down menu has * in it, the array should calculate everything rather than a specific value.
={IF(ROWS(I$14:I14)>$H$7,"",
IFERROR(INDEX('Sheet1'!$C:$C,SMALL(IF(('Sheet1'!$T:$T=Sheet2!$H$2)*('Sheet1'!$U:$U=Sheet2!$I$2)*('Sheet1'!$L:$L>0),ROW('Sheet1'!$T:$T)),
ROWS(I$14:I14))),"")}
In the SMALL array I have three conditions:
1.'Sheet1'!$T:$T=Sheet2!$H$2
2. 'Sheet1'!$U:$U=Sheet2!$I$2
3. 'Sheet1'!$L:$L>0
The array works fine but only if I place an actual value in the drop down cells which are H2, I2. The third condition is just to ensure it only calculates the array for rows that have numbers above 0. My question is how can I amend it so that selecting "*" in the drop down cells will calculate for everything?
View 3 Replies
View Related
Aug 11, 2014
The worksheet is basically a sales funnel and I want to use a drop down menu selection in ever row of column Z to update the relevant cell, based on the selection, in the same row, column Q to Y, with a date stamp which will then not change as the drop down is progressively changed to suit the relevant stage of the job.
View 2 Replies
View Related
Jan 11, 2014
I am trying to build a complex equation that has multiple parameters filled by several choices from several different dropdown menus.
For instance:
I'm building a set of 5 dropdown menus, that each lead to filling certain boxes with a number created from a formula. I've provided a small sample to explain what I'm trying to do.
My problem is, I'm unsure of how to make it so certain drop down menus affect the information inside of the next. Like, I want to have someone select "Oscar" under the characters, and have the "Lances" list come up in the Weapons column menu. I don't want the Weapons menu to be selectable in any way unless a "Character" has been selected.
View 4 Replies
View Related
Dec 9, 2011
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.
View 1 Replies
View Related
Jun 26, 2014
I work for a Machine Shop in the Toledo area. We use a quotation sheet to quote our products to our customers. It has 3 cells that describe the Customer, the Contact person at that customer and finally that Contact's Email Address. I have a drop-down menu in each of these cells. The first, Customer, is based off a simple list of our customers. The Contact drop-down then uses the Indirect Function to search the worksheet for that Customer. The drop down is actually based off of a Range Name saved as that Customer's Name.
However the problem is many of my customers have & or , and since Excel does not allow those symbols or even spaces in a Range Name I have to use Substitute to be able to keep the spaces and the & or ,.
It is important that on the Quote Sheet the Name of the Customer is identical to the actual name of the company so I cant use AND instead of &.
Currently my Second drop down, Contacts, has a formula that looks like this {=INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($H5,"_"," "), "3", "&"), "2",","))}.
My problem is that when I give the Range Name a Name , my drop down doesn't work and Excel tells me that the above formula results in an error.
View 6 Replies
View Related
Mar 20, 2007
I want to create a spreadsheet, where one column allows the user to choose 'P' or 'C' from a list. No problem. I want the next column to contain options specific to what they choose. So if they choose 'C' they get a different list to choose from than if they would have chosen 'P'.
View 11 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
Mar 18, 2013
I am trying to have a drop down list which is dependant on the result of a cell.
For example, if cell A1= CAT, then cell A2 should have a 'drop down list 1'; if cell A1=DOG, cell A2 would have 'drop down list 2'; if cell A1=RABBIT, cell A2 would have 'drop down list 3' etc.
Creating the drop down list is easy, I just want to know if it's possible to have different drop down lists depending on the result of a cell.
View 1 Replies
View Related
Jun 10, 2014
Attempting to use INDIRECT function to create dependent data validation list (Type). The tricky part is that the drop down options are dependent on the values of two (2) cell values (Brand and Country).
I have used the INDIRECT function before but dependent on ONE cell value only.
Have attached an example to better explain.
Example.jpg
Example.xls
View 4 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
Mar 31, 2007
i got this problem with my excel spreadsheet. I wanted to hide different row when i select different option from my dropdown list. At start, my B3 cell is empty therefore every row is shown. Thing work fine when i select any of the option(a,b,c,d). However when i changed my option from "a" to "b", row1 is still being hide. What can i do to refresh it?
View 14 Replies
View Related
Apr 1, 2014
I need to create a 2-stage dependent drop down list.
Stage 1:
In cell G7 (on a tab called 'Data'), I have created a drop down list based on a range name called 'Region' .
Stage 2:
In cell G8, I need to create a drop down list which is dependent on the value chosen in G7.
Problem is, I will need to create a dynamic range name here which needs to vlookup or match the value from G7 in a dynamic list of data (the data is on different tab called 'RCA Data' and is spread over columns A:E with row A for headings - the data will go down an undetermined number of rows)
Every time the value in G7 matches a value in the 'RCA data' (the potential matches are stored in column
D) I need the corresponding cell value in column C to be added to the dynamic list. This list then needs to be available to choose from a drop down list in G8.
View 1 Replies
View Related
Jun 20, 2014
I need to create a data validation drop-down that is dependant on what has been selected in another drop-down. However, all the solutions I've come across online so far suggest using the INDIRECT function, which is not appropriate in my case. This is because the data upon which the two drop-downs are based on is itself dynamic. My specific situation is this:
In one sheet, I have a list of university names in one column, and in the next column a list of faculties. The same university can appear multiple times in the sheet (once for each different faculty at the university). This information will be added to regularly, with new universities and new faculties added often.
In another sheet, I want a drop-down in one column that takes its validation from the list of universities. I then want a second drop-down that is only populated with a list of faculties at that university. The INDIRECT solution seems to only work if I can define a static range of cells from which to draw this data. How do I do it in my situation above?
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 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
