I am creating a class list from a list of all students and classes. Names of students are in column A, their class name is in column B. In another sheet I would like to show a list of names from one particular class, say "Y8/En1".
Using a filter on the original list isn't an option as I will need it to pull/show other things eventually.
if there was a way to automatically add each student to a class list as their information is inputted. What i mean is, after input Sally Student in the main enrollment list she would automatically go to the class list for the class she has chosen.
I have attached a sample workbook with my desired results. Basically what i want to do is make this easier for those in charge to get the class lists from the main list without having to copy and past all the time.
The class lists are in the tabs in the workbook.
Any way to create a list in a column from other lists in other columns?
View 9 Replies View RelatedI need to create a list of all available variations from 2 lists. Example, I have a list of data that has 50 entries in it, and another list that has 300. I need to create a list of all options to import into a system that would include all 1500 options if you were to make a list of all possible combinations of the 2 lists of data...
EG. list 1 = 1, 2, 3, 4, 5 and list 2 = a b c d e
Final list would be
1a
2a
3a
4a
5a
1b
2b
3b
4b
5b
1c
2c
3c
4c
5c
etc...
I have a number of text columns, side by side in a worksheet, containing names. I wish to combine these columns into one new column, removing any duplicates, to create a single list of names. Is there a simple way to do this?
View 3 Replies View RelatedI'm using Excel 2010. I have a workbook that has six or seven worksheets in it. Each worksheet has a header row, and then lists of clients at the clinic where I work. The columns are last name, first name, medicaid #, social security #, DOB. We use these lists every month to verify that each client has Medicaid for the month. Therefore, there are also columns with names like April, May, June where we mark yes or no for each client. Some of the worksheets also have information about the clients' guardian, phone number, etc., but not every worksheet has those. (I can't show you the worksheets b/c of federal privacy laws, of course.)
I really, really want a worksheet that's a master list of the data from each of the existing worksheets, and I'd like to keep it synced to the source worksheets. I'd love it if I could also add a column telling me which of the source worksheets the data originally came from.
I've tried a few things to make it happen, but nothing has worked.
I've tried using the Consolidate function built into Excel as well, but that only works with numbers, not text, and it wants to manipulate the numbers instead of just copying them over.
I've found several macros online that should have worked (including one from this site) but that I wasn't able to install to my worksheet. I followed all the steps for installation, and throught it worked, but when I went back to the Excel file to run the macro, the list of available macros remained emptpy.
Is it possible that corporate IT has disabled the ability to install macros? Is there somewhere in Options were I need to go to turn Macros on?
I have a problem with a dynamic userform that I need to create.
I need a userform in which the number of controls are determined by the some values in the worksheet. The users of the workbook must be able to add new controls to the form, so I do not know the number of controls in advance. I therefore can not create the userform through the design module but must create it through code instead.
I need a userform with a number of comboboxes and a commandbutton, which when clicked sends the content of the comboboxes to the first empty row in a worksheet.
My problem is that I can't assign any commands to the comboboxes (which are created at runtime), nor can I use the values of the comboboxes in any commands assigned to other controls in the userform (e.g. the commandbutton).
I have read as far that I probably need some form of class module to create the controls at runtime, but since I am relatively new at VBA, I'm having trouble getting the class module to work in connection with the userform.
As you can see in the attached file, I have a column of classes , a columns of related revenues and the total for each class. I have to create a column, next to these, where I can create the percentage with respect the sum of each class (as you can see by the formula). My problem is I am not able to say vba to find the totals and to create the percentage just with the revenues related to that total.
p.s: I have to work on excel2010.
I need to consolidate these two lists of data into one list.
First set is just a basic list of individuals with their data.
Second set is multiple entries for those same individuals. Each entry shows a subscription to a programme. The final single sheet should have one row per individual which shows all of the programmes they are subscribed to.
See attached, example sheet.
The real list is 3000 IDs, so need some kind of formula to do this.
I would like to know if there's a way to create a checkbox list in a cell that influences other cells:
For example, I would have column A as "Materials Accepted", and each cell would have a dropdown list with the options "Wood", "Drywall", and "Metal", in checkbox format. Then to the right of that, I would have each of the Materials as actual columns so that column B is "Wood", column C is "Drywall", and column D is "Metal".
The way I would want it to work from there is that whatever options I select from the dropdown, it would then put a TRUE flag in that column. So if I select "Wood" and "Metal" on the dropdown checkbox list, columns B and D would both read TRUE on that particular row.
Just wondering if something like this is possible, as I wanted to apply this concept to a much larger scale (About 29 columns for 29 Materials, scrolling left and right is really time consuming). So far I've only seen guides on how to create a SINGLE checkbox on a row, and none with multiple checkboxes that would function in this manner.
I have been trying to create a userform that will generate a numerical list starting in a given cell using a starting number, an ending number, and an increment that the user will imput into a textbox. I have a userform set up, but I do not know how to do any coding. I have very little experience with excel.
View 2 Replies View RelatedI have values in Worksheet 1, spread over A1:D25 and A200: D250.
In worksheet2 I have values again from A1:D25 and A200:D250.
Is is possible to only get the unique values of those 4 ranges with the advanced filter? They all need to be shown in eg worksheet 3 starting in A1, (so kinda merged in a sense)?
Is that a thing more for a UDF, or is there a excel function/option that does exactly that?
I have been looking for ages for that kind of function/option, since I thought it must be possible. But this sure does not look to be a standard functionality, or is it?
Is there a (free) add-in that might do this kind of thing?
I found this code on some office help page:
Sub SortAllRangeData()
' Place column header for temporary sort area.
Range("IV1").Value = "Numbers"
It kinda does what I needed, but it lists the actual data in the same spot it used to be. I want to be able to list the sorting in a different column on a different sheet and in 1 column only. Is this difficult to modify so it becomes a UDF or is this something totally different?
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 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.
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 need to create a scroll list where user can scroll the contents and see and select a perticular Item and whe he does it (selected) should display on the next cell
View 2 Replies View RelatedI have been using Excel to create equipment lists for my work. The forum has helped me by creating a visual basic macro to create a 'final list' sheet compiled from the other source worksheets.
On my 'final list' sheet I need the first 10 rows to be left out of the macro’s so that I can add header information like job reference etc. I have tried altering the visual basic code by adjusting the row numbers in it so that it starts at row 10 but this keep's on producing error codes. This is due to my limited knowledege of what each line does.
I also wish to be able to paste new equipment into the final list sheet. When I do this at the moment the code does not recognise it is there and does not carry the information accross back into the source sheet. It is the same when I block select an area and delete the code does not recognise I have done this which is fustrating.
I am working with a class module and some fields require specific inputs that another coder may not intuitively know. How can I create a list of acceptable conastants that will appear as a drop down when the user is coding with the custom object?
For example:
I am creating an Email Engine object that will handle all my emailing needs in code, because coding emails is such a pain. Then I can just create the blasted thing, import the class module where needed and never have to worry about it again. Some coworkers are interested in using the module for their projects when I am finished. I want to put an option for message flags in the object, but only certain colored flags are allowed. How can I get the VBE to show a little drop down with the acceptable flag constants when another user types
[Object Variable].MessageFlagColor = {drop down list of options}
I started with
Public Property Let MessageFlagColor(Value As Constants)
End Property
But that hasn't gotten me anywhere.
I'm having difficulty creating an array formula. In a multi-column sheet, I am looking at a column with classes and a column with a date (in the format 7-Oct-09). I need to list the number of a specific class for a particular month (any day). I have tried the following which gives only the number of classes:
=COUNTIF(A4:A2500,"AA")+COUNTIF(H4:H2500,"10/??/09") and
=SUM((A4:A2500="AA")*(H4:H2500="??-Oct-??")) which gives me 0. Maybe an array formula is not the way to do this.
I cant seem to find the correct syntax for creating 14 validation lists using array members as the source of the named ranged. The validation lists are stored on a different worksheet, the Named Ranges are created fine, as are the ranges that are having the validation applied. The Syntax I am having a problem with is
Public Sub assignDVList(WSD As Worksheet, sListName As String)
Dim DVListName As String
DVListName = "DV" & sListName
Application.Goto Reference:=sListName
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & DVListName
It is the Formula1:="=" & DVListName that is creating the headache. The sub is called as the array moves through the columns, using the header row as the Name for the Named Range, and the data Validation worksheet uses the same naming except it has DV in front.
i've reached a point where i would like to learn more about class modules but i have no resources. When i create a Class object can i visually see it? i want to know as i'm looking to use them within userforms if possible
View 4 Replies View RelatedImagine this..
In Column A I have 500 email addresses.
In Column B I have 2000 email addresses.
Now in Column C, I want to have all the email addresses in Column B MINUS all the email addresses in Column A.
IE: So basically if Column B had 300 of the same email addresses in Column A, then Column C will have 1,700 email addresses (2,000 - 3000) = 1,7000.
I have been having alot of trouble and have read alot. I can't seem to figure out the problem. I am currently working on an excel sheet that will select a name from the first drop down list. Based on that name five other list will be generated just on the first selection.
View 3 Replies View Relatedi have consolidates five paired lists on same worksheet into a new list? Each pair of columns (First Column (Acct #) and Second Column (Day N Count)) contains numbers and the range of data in each column and/or row will be unknown each week, so need the formula to auto adjust. New list should have this format:......
View 4 Replies View RelatedI have a set of unique customer names in a column A, say A1 to A200 in my data sheet. These customers have route numbers assigned to them (defined by the client) column B - B1 to B200. The route no.s are 1,2,3,4,5,6,7, & 8. The column lists can be of varying lengths but no more than 500 rows. I want to relate the routes to 8 separate sheets in the workbook. In each sheet I want to extract a column of customers based on the route number. So if the first customer in column A is route '1' (column B) it populates the first entry in sheet 'Route 1' with the customer name as so on for the rest of the sheets.
View 9 Replies View RelatedHow to do a third dependent list with the last two list depending on the first. The con textures site does not explain this.
View 1 Replies View Relatedi have two date lists
list 1 dates a1:a100
list 2 dates b2:b100
i want to extract the date from the two lists mentioned above in on list start from c2 taking in consideration the dates to be in sorting from A-Z when it extracted in the one list
i need formula not a macro
I have dropdown lists in a form and i want to make it so that the user can not input a value other than the ones in the list, how can i do this?
View 7 Replies View RelatedI have a question regarding the data validation lists. What i have is 4 different lists
Department
Assays
Instrument
Manufacturer
What I want to do is...once a user chooses a department, he gets a list of assays done in the chosen dept. and also a list of instrument used by the dept and a list of manufacturers
I used the examples shown here
http://www.contextures.com/xlDataVal13.html
http://www.contextures.com/xlDataVal02.html
and was able to succeed in creating dependent lists to some extent...I am attaching whatever I have able to accomplish so far, which is able to pull up the assay list based on the dept. ...but am confused as to how to get the Instrument and Manufacturer list at the same time .....please choose 'Chem 2" from the dept list to see what I have managed to do so far
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)?