Creating Dropdown Menus With Extensive Changing Content
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
ADVERTISEMENT
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
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
Oct 11, 2009
I have a workbook which uses the code below to create a new menu upon opening. I have now used this same code in a second workbook to create a different menu. both work OK on their own However if by chance both workbooks are opened at the same time only one menu is shown. Is there a way I can change one of these to create a second menu if the first one is already there?
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
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
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
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
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
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
Jun 9, 2008
I am using the following code to create a custom command menu.
Sub AddMenus()
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCustomMenu As CommandBarControl
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("&New Menu").Delete
On Error Goto 0
Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar")..........................
I want to add a new button control at the bottom of the menu underneath the holiday control but it keeps adding it on the sub menu, which leads off the holiday button control. I want the menu to look like this.
Open Net 2 Access
Add Employee
Edit Employee
Delete Employee
Holidays (3 options on sub menu)
*New Control
View 4 Replies
View Related
Feb 22, 2007
I have a combo-box in Sheet Number 1 filled with date. In all my other sheets there a empty combo-boxes. When the workbook opens I automaticly want to copy the content from the combo-box from Sheet 1 into all the other comboboxes in the other sheets. Is there an way to solve this problem with a minimum amount of loops?
View 2 Replies
View Related
Jan 3, 2014
I am trying to make a dynamic chart with a drop down/cell selection. I have a table with names vs. months. Each name/month has an associated value. I want to display a bar chart that I select with a drop down. i.e. If I select January, the chart will show january's data. If I select February, february's. Etc.
I tried to set my series values as: =indirect($C$1) where C1 is the entry cell for the month and the value corresponds to a named range. I get an error that says "That function is not valid."
When I try to put just the named range, excel highlights the correct range but then tells me that "the formula you typed contains an error. Try one of the following..."
View 3 Replies
View Related
Oct 8, 2008
Is it possible to set the default contents of a cell instead of having it blank? I want a cell to say "No Data" if you have not entered any data into that cell, and when you do, then it is replaced by whatever you entered. If you delete the contents of the cell, it goes back to the "No Data". Hence the "No Data" being the default contents of the cell.
View 5 Replies
View Related
Jun 6, 2006
I am looking to build a macro that will perform several tasks. This is what I need.
First I want it to look at column D and delete any rows that do not contain the following DEP-CHK, DEP-SAV, or DEP-MMA.
Next I want it to look at column E and delete any rows that do not contain
the following: 000, 503, or 910.
Third I want it to Sort all data by Column I then B then C.
Next I want to insert 2 blank rows each time there is a change in column I.
Finally I want to sum all of the sections in column F.
View 9 Replies
View Related
Feb 6, 2012
How can I create a msg box which ask's for Yes or no option before clearing a sheet with all contents?
if code is this:
Sub Clear_sheet()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
If Not ws.Name = "master*" Then ws.Cells.ClearContents
Next ws
End Sub
View 4 Replies
View Related
Oct 14, 2008
I have created two buttons in a worksheet to navigate to two other worksheets. I would like a cell to display 'YES' automatically if one of the two buttons is clicked and or to display 'NO'. I also would like to change the colors of these cells change automatically depending on which button is pressed.
View 2 Replies
View Related
Jan 8, 2007
I am quite a novice with excel and I am trying to get a cell to show blank when I select a drop down list value in another cell.
Cell D2 contains a validated list containing two items (Air, Vacuum).
If I select Vacuum I wish to change another cells (D4) contents to blank, D4 is also a validated list containing three items (Yes, No, BLANK) the blank is actually a blank space and not the text blank.
I can get the cell to operate correctly but if I select Air in Cell D2 and then Yes in cell D4, but then change my mind and reset to Vacuum in cell D2 i need the cell D4 to clear its contents automatically.
View 9 Replies
View Related
Jul 9, 2014
I am working on a code which works, however, its is not working entirely as i intended. In this example, the body of the email should contain content from textbox.
I have 3 text boxes, not all are expected to be filled out. So if only textbox1 is filled out, the body should only include information derived from textbox1.
Whats happening now is that even if textboxes 2 and 3 arent filled out, the body of email produces blank formulas from textboxes 2 and 3. So how do i fix this code so that if I enter data into textbox1 only, the body of email will not include empty formulas? Ive also attached a copy of the Produced email file. Code in question is in red font
Attached Image : 7-9-2014 11-21-35 PM.png
View 4 Replies
View Related
Nov 28, 2007
I want macro to run automaticly when I change values of any of the cell in worksheets("Data").range(B2:F1000)
Is there any way to write macro only for that worksheet/range?
View 9 Replies
View Related
Feb 3, 2010
From this link: [url]
there is some clever code to create a customized menu in a dynamic Add-Ins section of the Excel ribbon; this section disappears when the workbook is deactivated and re-appears when it is activated. The custom menu has 2 main items each of which can launch a macro. There is also a 3rd main item called "Next Menu" which cascades nicely to a sub-menu. That sub-menu can have several items of its own. Very cool code.
However, I am trying to add a 4th item in the main menu, say, "Next Menu2" which also cascades to a sub-menu. The problem I'm having is when I try to replicate this code to accomplish this, "Next Menu2" appears in the sub-menu of "Next Menu", rather than as a separate (4th) main item below "Next Menu".
I believe there was another post (not sure of the date, but has since expired) which asked basically the same question as mine. There was a reply with code saying "This should work" but when I tried it, it didn't help.
View 3 Replies
View Related
