Looking to create a drop down list on the top of a column that will find all entries with that numerical value. For example, lets say I have four entries with the value 1234 spread out through the columns. When I click the drop down menu, it should only have 1234 once and when I click it, it shows me all the rows with that entry.
View 2 RepliesI have two lists. A1:A7000 has 7000 items and B1:B10000 has 10,000 items all in random order. List B includes all the items on list A, but with an additional 3000 items. How can I extract the 3000 items that are exclusively on list B? I would like to put these on a new list C1:C3000.
View 6 Replies View RelatedI am new to writing macros and am wondering what is the easiest way to create a drop down and select multiple items separated by comas. The people who are going to input data into this spreadsheet are field people and not technically savvy so need to design something that is just a matter of cliking on the drop down and selecting multiple items and they should show on the same cell separated by commas.
View 9 Replies View RelatedI did all the search for multiple drop box or list box that can connect to other list boxes but no luck.
It's the same as when you surf with Internet Explorer, you can click "Views" in menu bar, list box appear and then choose "Encoding", another drop box appear, then when you choose "More", then another box appear.
Is it possible to have this sort of hierarchy list box in excel where drop box connect to another drop box?
I am trying to create challenge/response cards. Each card will have a 5-digit number in the upper-left and lower-right corner along with a series of rows containing a line number and 3 pairs of challenge/response words.
Rather than have have copies of these cards, I want the central office to have a macro-enabled workbook wherein they can enter the two numbers from the card and have the challenge/response matrix regenerate on a worksheet.
So, essentially, I want the challenges random number generator seeded from one of the 5-digit numbers and the responses random number generator seeded from the other 5-digit number.
I am trying to use
Code:
Randomize(Seed)
where Seed is one of the 5-digit numbers.
The problem appears to be that, no matter what I use for Seed, the RND(-1) function is not resulting in different numbers.
I am using Excel 2007 and Excel 2010.
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 am trying to create a simple user interface type thing so that someone is able to select from drop down lists someones information, such as whether they are male or female, aged between 19-35 or 35-67, whether they are studying in a business area, legal or construction etc (there are 6 variables in total), This will then give the probability of success of the person passing this course based on probabilities which I have already worked out. I have worked out how to do the first stage of creating a drop down list showing alternative choices with Sex, Age etc in the data validation options, however:
There are 517 possible combinations, as in Male aged 19 to 35 studying Business (with other variables) or Male aged 19 to 35 studying Law (+ other variables) etc etc etc each with their own probability of success. Due to the long nature of writing out Male1935BusinessNorthWestWhiteBritishCollegeBrown I have rewritten it so it appears in the excel file as M1935BNWWBCB, which obviously wouldn't make any sense to someone if they had to select M 1935 B NW WB CB from drop down lists.
Along side the M1935BNWWBCB there is the probability of success specific to that type of person. So for example I could would have:
M 60%
M1935 64%
M1935B 35%....
I know how to use array formulae to create a unique list, i.e.{=INDEX($G$1:$G$760,SMALL(IF(ROW($G$1:$G$760)=MATCH($G$1:$G$760,$G$1:$G$760,0),ROW($G$1:$G$760)),ROW()))}
however this is giving all the unique items from column G and I only want the unique items that have a non-zero value in column H as well. This would be the sumif of all instances that would have to be zero. I've tried to crack it and I've tried to search for solutions but so far no joy.
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.
I have two worksheets. In the first one column holds the names of universities, and the next column holds employee names. For example:
London Metropolitan University | Chris Davis
London Metropolitan University | Sean Joesbury
London Metropolitan University | Stefano Carlino
London South Bank University | Simon Forsbrook
London South Bank University | Peter Lacko
University College London | Ben Burch
University College London | Oli Lan
In the second sheet, I'd like the universities to appear as headers to the columns, with all the employees of that uni to be listed underneath. I'd also like for these lists to be updated automatically as new rows are entered in the first sheet.
I have a column of DV lists. Is it possible for me to make excel, go through each DV list, and randomly select an item from each DV list?
View 1 Replies View RelatedUsing 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?