I am creating a form where the user chooses a Region from a drop down list and a Level from another drop down list. I have a cell I want to populate with the amount pertaining to the Region and Level from a seperate spreadsheet within the workbook.
The levels for each region go from 1 to 12 and there are 4 regions from A to D. I have separated each region and named them GradeA, GradeB, etc.
I have a list of names and a seperate list of names with emails.
In column A there is a last name, column B a first name, and then for the second list in column D there is an email.
I need to tell Excel that if column A and Column B both have a replication on the list, then in Column D populate it with the email address on the list.
I know this is confusing so I have attached a sample of my spreadsheet. The email address is in yellow and I need to have it copied to the cells in white. The list is almost 14,500 lines long and it would take forever manually.
I have a workbook with several drop down boxes and formulas already set up and working. I want to improve it though. My question is.... is there a formula that will make the drop down list change based on a cell value....
For example: If B6 equals vegetables then C6 equals list (potato, carrot, pea, etc.) If B6 equals fruit then C6 equals list (apple, banana, grape)
Is there a way to make a drop-down list optional? When I create drop-down lists the user has to make a choice from that list.
Example: a list of doctors. I need the user to be able to select from that list if the patient saw one of those physicians, but if the patient saw another doctor not on the list, I need the user to be able to enter the name of that other doctor. Currently the user has to select from the list or not enter a doctor. I am using Excel 2003.
I have 16 seperate titles and a different figure for each title.
However, these 16 titles are repeated hundreds of times, in different orders, down a column. I need to populate the list with the numbers that belong to each title.
I don't have v look up on my computer. I am useless at excel.
I have Columns A:D with a heading at the top. the user can then type organisation names under the correct column. There is a maximum of 30 entries (rows 2:32).
Problem One:
Column E is a hidden column and I need cells E2 and downwards to be a list which gets populated by ALL the organisation names inserted into $A$2:$D$32. I don't want there to be any blank cells until the last entry in column E (if that makes sense). how to populate this list?
Problem Two:
At another part of the worksheet, I have five cell where the user has to select from a list. The five cells correspond to the lists in columns A:E. Is there any way to avoid having blank options in a list using data validation (say if the user has not entered 30 organisations into one of the columns)?
There's a table with all the raw data in it
camp name
session name
to put in the correct format I am thinking of usinf drop down boxes so that the user can selct the camp name from drop down and based on that the session name can be populated and alos the rest of infomration of that accput in to the specified fields
I've created a spreadsheet that has two drop down lists. Based on what the user selects from the first list, the second list is populated. It's working great except for the items in the first list that use special character, such as a -, or &. If necessary I can upload a small example of how I have my sheet set up, but I think I found it on this site!
View 4 Replies View RelatedI currently have a huge data sheet with multiple columns of information. It contains a list of projects organized by columns with information pertaining to each project. I've named this worksheet data.
On another sheet I've named Present, I'm trying to find a way to reference a single project at a time using a drop down list. The Present Sheet has a list of characteristics fields that need to be filled with information from "data" worksheet. I want the characteristics to change whenever I choose another project name from the drop down list.
How to make a drop-down list appear when a cell is double-clicked as opposed to just clicking on the box to the right of the cell that appears when you click in the cell? I want to be able to use the ability to drag-copy the cell contents from the little square in the bottom-right of the cell, but cannot do this when there is a validation drop-down list!!
View 14 Replies View Relatedwhy is it that if i use the drop down list to select something i then cannot go back the drop down list to select something else if ive made an error?
has it anything to do with vlookup?
As part of my project I have to create a data extraction excel sheet which is used as a template to extract relevant information from journal articles.
From each article I need to extract information on multiple psych. scales. For example, lets say Article 1 has information of ScaleA and Scale B so I would enter the means/sdev./... on each Scale from that article in respective columns. Article 2 might have info on Scale A and Scale C so I would enter them in respective columns.
As you see there are about 8-10 Scales with many subcategories (means,sdev,...), so my question is there a way to create a drop down menu such that all the scales (A-H) are in that menu and upon selection of a particular Scale the sub columns (with mean,sdev.,...) open beside/underneath it. I hope you understood my question.
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 ....
View 13 Replies View RelatedI read this thread which helped me very much. So I was able to link one drop down box to a list, which fills in the adjacent box. Now I need some help getting an average.
Ex. Drop down 1 has a list of names which applies the associated numerical value in box 3. Drop down 2 has a list of the same names with the same associated values, and I need these 2 values averaged and put in box 3.
The problem is that Drop down 2 doesn't always have a name. It may be left blank and so I need drop down 1 to continue applying to box 3 when drop down 2 is empty.
This is what I have for drop down 1 to apply to box 3.
=IF(C2="","",VLOOKUP(C2,Sheet2!A3:B18,2,FALSE))
I've got an IF statement that's based around what's chosen from a drop-down list. The problem is I'm using "" to stand for nothing being chosen in the cell in question, and this is returning an error message and highlighting the '""' (that looks confusing).
I've tried checking and unchecking 'ignore blanks' in data validation but nothing much has happened.
Here's an example of the formula I'm using:
=IF((AND(C40=A,D40="")),C9,(IF((OR(C40=A,D40=DA)),C9/2,"")))
The problematic "" is the first one.
I have a "form" type spread sheet that I have created where the user can enter different information (like name, company, addresses etc.). I have figured out how to create a drop down list. But what I would like to do is create on a seperate sheet a "data base" of information which will be "copied" into the appropriate cells on the first sheet depending on what the user selects from the drop down list.
The drop down list will list company names, which are stored on the second sheet. However, each company will have other data such as the company address (which will be entered in seperate cells with information like, box, street, town, province, country, code).
Now this is the clincher... The user must be able to add to the data list on sheet two and any new entry must automatically appear on the drop down list on sheet 1, and when selected it's "data" must be transferred to the appropriate cells on sheet one from sheet 2.
I would like to do something similar to wiL with an employee drop down list. As the user begins to type the name, the drop down would narrow the choices alphabetically or the user could select the drop down list then hit the first letter of the name and go to that letter of the list (i.e. selecting "M" to go to the portion of the list that starts with "M").
View 2 Replies View RelatedI'm tring a different way to use hyperlink to other workbooks on my network.
I'm using a dropdown validation box and a button. I want to select from the dropdown a link. Not sure if the validation box is the way I should do this, maybe a form dropdown.
Basicly, I would like to use the drop down select the link and press the button to GO! to link.
Is it something like
HTML Hyperlink.follow (A1.value)
How would I write a marco that requires a password when a certain word in a drop down list is selected, bearing in mind that when this word is selected certain cells in the worksheet are no longer locked - in other words the certain cell can only changed by a specific person in the drop down list which when this person is selected a password is required.
View 14 Replies View RelatedI have a drop down list in my excel sheet that contains names of manufacturers. What i would like to do (using vba) is display some details of each manufacturer that will be copied from a different sheet.
So my question is, how can i read the string from the drop down list on one sheet and then copy a cell from another sheet.
I have two columns B and C - In column B are the names of people. (12 names in this list) and in column C is a number from 1 - 4 (each person is in either team 1, 2, 3 or 4)
I have a drop down list in Column A which links to a small table (1 2 3 and 4) so the user can choose which team. I need a formula to then list the members of that team, when it has been selected.
I was using a Vlookup command, but this only works for the first person on the list, if i drag the formula down, it is still the same person. I need to show a list of all the members of that team.
I try and simplify a list of flights and their respective distances, so its easy for users to enter new flights into a database. Its not too much complication, but needs a formula i'm not sure which one though, either a pivot table or use of an OFFSET formula? Anyway a description of the attachment:
entries for all offices: title headers are self explanatory, with a drop down list at cell C42, referring to Destinations!G4:G88 (a range called airportlist)
cell D42 referrs also to the same range, airportlist. numbers of flghts: totals of how many flights are booked per office, i've got this one sorted. total flight distances: self explanatory.
destinations: a list of every flght the company takes, for each route of travel. also i've researched out the distance each journey covers. i've then compiled a list of each airport used, so i can make the drop down lists which are then used on the "entries to all offices" sheet. (this is the airportlist range).
on the "entries to all offices" sheet, i've made the sample drop down lists on cells C42 and D42, and what i'm hoping is that cell E42 displays the distance between the two cities displayed in C42 and D42.
so my magical formula needs to take the value in cell C42, then on sheet "destinations" needs to pair it to a row matching the value in cell D42, then take the resultant distance in that row and dump it in cell E42.
I've created a sheet with a number of drop down lists using the validation menu.
Whilst working on the sheet the lists have disappeared! The validation options are still there and refer to the correct cells that have the correct details in them, but when I click in the cell that should have the list in it I don't get the down arrow to click that shows the list.
I've also tried creating a new list in another cell but that does the same.
Hopefully i've just changed a setting that stops it working and I can unchange it - but I don't know what's caused it.
Using Office 2003.
I have created two workbooks, one which is a master price list (MasterPriceList.xls) and another which is a pricing sheet template that will be used to calculate many different products (Pricing.xls)
I have used a VLOOKUP across the workbooks (thanks to those who helped me with that) but I cannot get a drop list to work across two worksheets. When I enter the source in the "refers to" box, I get a notice saying that you can't use data validation across two workbooks. However, this was the same error message I got when I was incorrectly inserting the reference source for the VLOOKUP function, so I don't believe it Also, there are several tutorials on the web that say this can be done. However, none of them seem to work for me.
I cannot open the Data Validation box and get to the other open worksheet, so I can highlight the area I want, with the range I want to drop down. Until I close the data validation box, I cannot get out of that sheet. I have used cell ranges as well as named ranges.
When I type in a name, I get only the text I entered in the source reference box appearing on the sheet, i.e., the drop down box will only show "=C:Documents And SettingsAllenMy Documents, etc" it doesn't seem to recognize it as a source.
i know how to make a drop down list and then I can strech it over X rows but how do I set somthing like from C3 and forever onwards (C4,C5,C6..)use this droplist. I do not know how many entrys will be made so from C3 and onwards I need all cells to have this drop down menu.
View 2 Replies View RelatedI'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.
Excel 2003. I have created an Excel purchase order form that has several drop-down lists that work just fine. However, I have yet to figure out how to change the font size and be able to use such things as BOLD, etc...even when I format the source data list. I can format everything else in the form...except how the drop-down list data is displayed.
View 2 Replies View RelatedI can create a drop list show various choices, is it possible to have the list of choice but also for the user to input text not in the list.
So if you had a list of names andrew, brian, chris and then not in the list you could write david ?
I have already have my lists created.... Problem... in Cells A17:A62 I need
the list of employees names to display down the column by selecting the
number value in Cell A13...(A13 being a four digit crew code)... Each
employee has a four digit crew number associated with them.... so far I have
Cell G10 as a drop down list to select the crew name, which puts the crew
four digit number in A13. So now I need Cells A17:A62 to display the
employee names down the column by using the value in A13... does that make
sense?
From the Excel Toolbar - Data/Validation - I'm successfully using a validation list [drop down arrow] to be able to choose from a list and have whatever value I choose entered into the cell. So far so good. Everything working fine.
Here's the thing though, the list I'm referencing is a column with 50 rows. Sometimes there are many items in the column - up to 50, and sometimes only 3 items depending on other variables in the spreadsheet.
Because the list is referencing the entire 50 rows in the column (only way I know how to do it), during times when the column has only 3 items (3 rows), the drop down still shows a bunch of blank spaces (like 47 of them - tons of white space with a scroll bar window that runs down beyond where one can see), which is awkward (strange looking).
How do I program the validation list to only show as many cells within a range that have actual values within the cell, or where <> "" ? Or, in other words, to leave all blank spaces found in the list = NOT SHOWN.