I have a workbook with several drop-down lists. The names for the lists are on a seperate sheet in the same workbook. I have used data-validation and allow list in all the cells with drop-downs. I have used =NAME in the source. I opened the workbook this afternoon for the first time in a couple of days and I am no longer able to select from a dropdown. I can type in the cells, but if I type something not included in the list, I get the "The value you have entered is not valid" error. So I feel like the validation is working on the cells, I'm just not able to select from a drop-down list. "In-Cell dropdown" is selected.
I'm trying to figure out how to make a spreadsheet where in one column a type of asset is entered through a drop down menu and then the next few columns are attributes - model, pattern, manufacturer, etc. - that would all have values entered through drop down menus populated based on the choice of 'type of asset' in the first column.
I can get a drop down menu for the 'type' column and a dependent drop down in the second column based on choice of 'type' using =INDIRECT(), but I do that by naming the dependent lists of models for each type after the types listed in the list of types. Once I've done that I can't name the lists of patterns after the types because those names are in use by the model lists. So how would I make multiple lists of attributes associate with the same list of types?
I have a work book that contains various sheets with different information. I have two different categories in one of the sheets,1 which contains counties and the other containing districts.
I have done a validation for the county which allows me to pick a county from a drop down list which works fine.I would like to create a drop down list for the districts aswell such that when i select i.e southyorkshire from the counties dropdown list,the districts drop down list will bring only districts in southyorkshire,excluding all the other districsts.hopefully i'll like the same to apply to all other counties.
Im trying to create a spreadsheet which has a drop down list in a cell, then when a selection is made a predetermined dollar figure will be entered in the cell next to it.
For Example:
Cell A1 Drop down list I have 20 cities listed, If I select St. Louis, I want the cell next to it to populate with $250.
Every city has a different dollar amount tied to it. So any time I select a city I want the specified dollar amount to populate the cell next to it.
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.
In worksheet 1 I have a list of employee numbers (column A) and and names (Column B)and in worksheet 2 I have a list of employee numbers (column A) and their payroll contracts (Column B). 1 employee can have multiple contracts).
What I'm trying to achieve in workshet 1 is to have a dropdown list ( Column C) which returns just the contracts that relate to the employee number in column A.
how to make few dropdown lists depending on one before.
Example: I have table with next data: COLUMN A: (A1) Expense Type ( in all columns under A1 are types of expenses );
(A2) total trip cost; (A3) Prize promo tour cost; (A4 ) court renting cost; (A5) Prize IPP cost
In columns B1, C1, D1, E1 are cities. Under each city is value for type of expense. I have uploaded worksheet for example.
Now, problem is next: I am trying to make drop down lists ( in new work sheet ) by using depending data from data table.
1.I made drop down list in first cell (let's say it is an A1 cell ) where I can chose city ( for example City of Zagreb ).
2.Then in next cell (B2) I would like to choose type of expense in drop down list ( for example Total trip cost ).
3.Finally, in C3 cell I would like to make drop down list that offers me just expense value for combination of chosen city ( Zagreb ) and chosen expense type ( Total trip cost ).
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.
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 have two identical drop down lists (months i.e. Jan, Feb, Mar ...) in two different sheets but in the same workbook. Is there any way where the user changes the month in sheet 1 & the other drop down list in sheet 2 changes automatically to the same month selected by the user in the sheet 1 or vice-versa?
I am trying to set up a spreadsheet with linked dynamic drop down lists, based on a table.Using the OFFSET function, I have managed to get this to work if the table is on the SAME sheet at the drop down lists. But I want to be able to put the drop down lists onto a different sheet, so that I can lock and hide the table.
I don't want to use the 'name based on the previous column' idea, as the table is large and will change weekly. the drop downs show all values, even duplicates, which I would like to get rid of.
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.
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 two drop down lists, one is named Copayment with values Yes and No and the other is named Coinsurance with values Yes and No as drop down lists. I want to make them mutually exclusive, for example: When the user picks Yes from Copayment, then the Coinsurance to be automatically No and the opposite: when the copayment is No then the coinsurance to be automatically Yes.
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.
I want to be able to reference cells in a table by using two drop down lists. One drop down for the columns in the table and another drop down for the rows in the table. The values in the columns will be different metal materials (aluminum, copper, stainless steel, etc.). The values in the rows will be thicknesses of those materials (1/16", 1/8", 1/4",3/16",3/8",1/2",3/4",1"). I already created a drop down list for the materials and another for the thicknesses. But now I want to be able to select a particular material and then a particular thickness and receive back in another cell a value that's cross referenced in a table. Specifically, the values in the table will be the weight of the particular material and thickness per square foot. I'll populate all of the cells in the reference table manually. I'll hide the table on a separate worksheet. i haven't created the table yet.
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 expect to deploy over 200 iterations of a workbook (templates if you will) containing a mix of up to one hundred different attributes each with its own list of valid values. My problem is the attributes don't always default to the same column in every template workbook. Therefore, to make things easier for users I would like to create attribute valid value dropdown lists on the fly using the "Valid Values' table as the source data (see the attached). Since the attribute names will be the same from one template workbook to the next, I wanted to come up with a macro that would reference a source table (a separate workbook) containing all valid values for all attributes which users would be required to download to their local machine.
See the attached example workbooks and an example of a "Valid Values" list. Here's what needs to happen: When a user opens template workbook #1 I need drop down lists containing the valid values for the attributes contained in cell B3 (User Code),C3 (Department) and D3 (Category). Likewise, when another user opens template workbook #2, I need drop down lists containing the valid values in cell B2 (Department), C3 (Group), D3 (Category), E3 (Level), F3 (Material) and G3 (User Code). Notice that "Department", "Category" and "User Code" are data elements common between both workbooks but resident in different columns.
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.
Trying to create a dynamic costing spreadsheet, where the user has to make multiple selections from many dropdown lists, all of which end up showing a total cost.
So far I have managed to create relatively dynamic lists, so that the choice in one cell will dictate what options are available in the next.
But if a user selects an options form all the dropdowns, and then decides to go back and modify a previous option to something else, the effected dropdown lists do not change until you click on the drop down arrow again and see the options available have changed.
Let me try and simplify... 'Menu 1' has two option: 'A' and 'B'. If 'A' is chosen, then the next menu, 'Menu 2', will have options '1' and '2'. If 'B' is chosen from 'Menu 1', then the options in 'Menu 2' will be '3' and '4'. Lets say the user chose 'A' from 'Menu 1' and then '1' from 'Menu 2'... AND THEN went back and changed their choice in 'Menu 1' to 'B'.... 'Menu 2' will still be displaying '1', even though the options are only '3' and '4'.
Can I make the dependant menu's either reset or blank or highlight to show that a change made somewhere else has made them currently invalid
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.
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.
Basically, I have an excel spreadsheet with different valued items for example,
TV Essential l £15 TV Essential extra l £20
Broadband Essential l £ 8 Broadband Extra l £12
I have options for TV, Broadband and Phone. At the bottom of the spreadsheet I want three drop down lists to pick what TV, Broadband and Phone package I want. For example: TV Essential, Broadband Extra and Phone Weekend. I can create the lists but I was wondering if there was anyway where, as you select the option on each of the lists, a running total will be created. So when TV Essential is selected £15 is added to a cell, say F20. Then when Broadband Extra is selected another £12 is added to cell F20. And as you select different ones in each list the total in F20 changes.
We have a proposal generation tool that we use that is based in Excel, and it works very well indeed. However, we're wanting to add a 'Custom Contract Generator' tab to the spreadsheet, and I'm at a loss with how to build it.
What it needs to do is :
- Allow the end user to select which contract clauses are required for the proposal they are working on, ideally via a range of dropdown boxes.
- Use that selection of clauses via dropdown box to create a compiled, formatted text list made up of those clauses.
- Enter that information into a landscape orientation Excel tab in such a manner as allows for tidy, business-suitable printing.
In short, I want the ability to pick what clauses we want to use, and have Excel generate a custom contract Terms and Conditions page based on my selection. And I've no idea where to start.
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.
