I am preparing a template for which I need a combo box. Becaue it is a template I can't have this combo box any specific input range. Because I won't know how long the data column (that wiill be used for input range) will be.
Also the data column will have many duplicates but the combo box needs to show uniques only.
Is there a way of setting the input range on a combo box to a vale set in a cell?
ie, if my current input range is A2:A40 and i then users add another 20 records to that column, i need the combo box input range to change to A2:A60, so I can leave the sheet unmonitored for the users to add lines and not have to manually change the input range of the combo box.
If I had a combo box that look at 10 item in a range A1:A10 and the output value was in B1
Say a user added a new Item in A11. How could I get the range of the combo box to automatically extend to A11 or A12 if two new items are added.
I am trying to avoid having the input range as A:A and having lots of empty values?
I could need a code for a Combo Box to selects the range in Column A. The range includes all non-blank cells.
View 9 Replies View RelatedI have a sheet of un-ordered data with duplicates. Looks a little like this:
Sheet2
*
A
B
[Code]....
What I want to do is: on a separate sheet, when the user inputs a Region name, it will return an array of unique Locations under that Region. For example, if the input is "NORTHEAST", the following would be returned:
Sheet3
*
A
B
C
[Code]....
sorting list with date ranges with formula.
I have a list of dates and I want sort them by growth, also only to show the values after specific dates. I know how to do it with Macro, I just need list changing dynamically.
Is there a way to prompt the user to pick an input composed of the values in a given column. Also, if there are repeats in the column, those need to be ignored.
View 9 Replies View RelatedI coordinate access requests for several contracts, and I have to list the approved accesses in a list where each line represents one person and one contract. For each access request, there will be an arbitrary number of persons obtaining access to an arbitrary number of contracts.
The input would then be as follows: Joe A and Jill B request access to contracts 1001, 1002 and 1003 ->
Joe AÂ Â Â 1001
Joe AÂ Â Â 1002
Joe AÂ Â Â 1003
Jill BÂ Â Â Â Â 1001
Jill BÂ Â Â Â Â 1002
Jill BÂ Â Â Â Â 1003
To automate this task, I have made a simple macro for generating a combination list of all persons having obtained access to a selection of contracts.
My macro worked well when I only wanted to list unique and independent list items, but now I have been asked to include each person's email address. How I can change my code so that only one email address is copied into my list for each person?
Code:
Sub AccessList()
Sheets("requests").Select
Dim rng As Range, c As Range
Dim rng1 As Range, c1 As Range
Dim rng2 As Range, c2 As Range
[Code]....
I have a range which will change in size & in content, & I want this to be a Named Range at whatever size it is.
Reason I want to is because I want to make a Validation List with this dynamic range. I also want a Validation list which lists the content of 2 or more dynamic ranges which may or may not be on the same worksheet - is this possible?
i.e.
First dynamic range: called "Milestones" at A11
Second dynamic range: called "Activities" at A25
& make a Validation list that will list content of both
I have been trying to make a combobox for which the list is a named range. However, this range needs to be transposed. ListFillRange doesn't seem to let me transpose the list first. I've tried transposing the list somewhere else first and then adding it, but it seems to want a range as opposed to a reference to a range. I'm so confused now. This is the basic code that I wish would work.
ActiveSheet. OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=253, Top:=472, Width:=117, Height:=20). _
Select
Selection.ListFillRange = "=transpose(Stream_Data!StreamList)"
While I'm at it, could someone explain to be how to refer to a combobox. ie. when you create it, you don't name it so how can you refer to it. That's why I have used the selection tool above to add the list.
I'm creating a sheet that takes three Validation Lists into account. The first two lists are based on named regions. The third list uses the first two lists to narrow a search down so that the user can pick what they want from a smaller selection. I'm using the code from Contextures.com to allow the user to double click the validation list to pop up a Combo Box. This allows me to show more than 8 selections at a time and also gives a little freedom on font and font size.
http://www.contextures.com/xlDataVal11.html
Since the third list is not based on a named region but rather code to compare the two previous lists, when I double click the third validation list a blank combo box pops up. it possible to pop up what ever lists are in the validation boxes.
This is the code that I have in the third validation box which States "The Source Currently evaluates to an error" but works fine for some reason. =OFFSET(CategoryStart, MATCH(B4, CategoryColumn, 0) - 1, IF(A4 = "Description", 2, IF(A4 = "ExtDescription", 3, IF(A4 = "PartNumber",4))), COUNTIF(CategoryColumn,B4), 1)
I tried making a named region that was based on the code, so that the list could change as the user selected different options from the first two lists. This gave me the same problems as I had before.
I know how to turn the autocomplete on and off but I don´t have a clue where I configure the data range or the cells to display the list when the combo box is clicked.
I am assuming that this is done in the Edit mode at "linkedCell" and LisfFillRange ... yet I can´t get this to work.
I currently have the following user inputbox that asks the user to enter the company name, of which it then copies the entered name to Cell E1 on the active sheet
Dim strName As String
strName = InputBox(Prompt:="You name please.", _
Title:="COMPANY NAME", Default:="Enter Company Name")
Range("E1") = strName
However, I would prefer it if there was a drop down list to select one of five pre-set company names without having the user to type the name out every time.
I have created a spreadsheet that has many dynamic ranges, one of which is a list of vendors that my company uses, named "Company". This range is in a spreadsheet labeled "Analysis Search". From this list and on another worksheet, I have created a drop down list via data validation where the user inputs the vendor, and should the vendor not be in the dynamic range, a prompt will appear and warn the user that the name of the vendor is not already in the dynamic range and to add it once the entry is complete.
What I am looking to do is for Excel to automatically add the vendor to the dynamic range instead of having the user have to input the vendor. Also, I have used VB to have the range automatically sort alphabetically, so I would like to keep that functionality if possible. Is something like this feasible in Excel?
I am trying to find a way to create a list of unique items from a named range. I have found a few solutions using filters for ranges that are contained in the one column but not named ranges that contain multiple rows and multiple columns.
Is there any way to copy all of the unique items from the named range to another location? Lets call the named range Table1 and it spans from B2 to J100.
i have a list of surnames in column A in a spreadsheet i want to use this for the source of the combo box also i want this list to be unique and if the surname is not present i want to be able to put the name in the combo box and add it to the sheet
View 2 Replies View RelatedI have a combo box on a form that is linked to a column of values allowing pre-emptive typing, or selection via a drop down. This works perfectly with one exception - each item shows up multiple times.
Is there any way of getting around this? the only way I can think of offhand would be to link it to a seperate table which is populated on a semi regular basis via another VB process? I would have thought this would have been a fairly common question but haven't been able to find anything on this board or others as yet.
I purchased the Excel Hacks book, and when attempting to do hack #53 with a drop down list, I can not get it to work. I do exactly what the book says to do, however, my chart and my drop down list are not linked together, and when I change a name in the drop down list, the information on the chart does not change.
View 2 Replies View RelatedMy spreadsheet has in the region of 30 columns, more will be added on occasion in the future and ultimately I want to have each of the column headers appear in a 2-tiered dependent combo box. In the following structure:
Category 1:
Header 1
Header 2
Header 3
Category 2:
Header 4
Header 5
Header 6
Header 7 etc...
What I'm not sure about is what the most efficent way of making it so that it will automatically add new Column headers (and possibly categories) to the drop-down box so that it does not need to be re-coded in the future.
I have a combo box (from the Control Toolbox toolbar). I like this to be populated with the contents of column A. however, in column A there can be duplicates. I obviously don't wan't any duplicates in the combobox.
View 9 Replies View RelatedI have a form with a number of fields. There is a field at the top of the form for Part number. When I type in a part number I need the form to auto complete any fields that are linked (if i can link them). E.g., if the part number is 111 then based on info in another table the fields will be auto completed for me.
View 2 Replies View RelatedI am trying to create a combo box whereby i need to populate a list of name into the box and when user click on the name, it will linked it to the corresponding letter in MS Word.
Have tried a number of codes available in this forum (to populate the combo box) but were not successful.
I have a dynamic named range which allows me to have an automatically growing drop down list in B1 as more cities are added in column A. however, my workbook will have N/A errors to begin with before cities are added.
is there a way for my drop down list in B1 not to include the N/A errors and only the cities? of course when the next N/A error turns into a city, i would like the B1 drop down list to recognise this and include it.
In A1 of Sheet1!, I need a formula that lists and sorts all the 'unique' values from range
ECWP!$O$6:$Q$1505
I have a dynamic table which is linked to a couple of charts. The table must remain dynamic.
I needed to add two new columns to the table, "Focus Area" and "Category". I need a drop down list in the "Category" column to be dependent on the item selected from a drop down menu in "Focus Area".
I can get the first row of the dynamic table to do this.... however; subsequent rows all lock the drop down list in the "Category" column to the same choices regardless of what is chosen in the "Focus Area" list.
Is there a way to make dynamic drop downs within a dynamic table?
I am making a basic inventory tracking worksheet which consists of 2 sections. The first section has all the unassembled parts and quantities and the second shows the total assembled parts. I am trying to track unassembled parts and assembled parts simultaneously. I would like to have a cell that I can put a number in (which would represent the number of assembled products manufactured that day) and adjust the totals in both parts accordingly as they are made. For example, if I start the day off with 5 units of all the parts unassembled and manufacture 2 that day, I want to be able to put the number 2 in a cell, hit enter, and then have the unassembled inventory decrease by 2 (per part) and have the assembled inventory increase by 2.
View 9 Replies View RelatedEach product is represented by a serial number (column A).
The can be sorted on column A from smallest to largest prior to calculating results if that helps.
The repair list contains 1 entry per spare part used, so the same serial number may occur several times.
Furthermore, a product may have been repaired on several instances - so the serial numbers can span several dates (column B).
The solution i am looking for should return the number of unique repair dates per serial number. That way i can see, how many times each product has been repaired. Results can be displayed in an individual column.
Sample list:
Serial........Repair date
207742052008-09-04
207755082008-12-17
207755212008-12-31
207755212009-01-22
207755212009-01-22
207755212009-01-22
207755212009-02-13
207755212009-07-24
207755362009-05-20................................
I have a data table that repeats as follows:
CITI
SCB
SCB
SCB
SCB
SCB
SCB
SCB
RBC
RBC
RBC
RBC
From the data above I need to make a new unqie list that would grab all unique entries.
CITI
SCB
RBC
I am trying to use the following guide:
http://blogs.techrepublic.com.com/howdoi/?p=111
Get stuck on "Listing A:
I have a worksheet in which Col. A contains the names of London boroughs and col. B contains the name of each Ward in that borough. I'd like to create a list (or combo) box showing all the London boroughs, and a second list (or combo) box which will show all the Wards for the Borough selected in the London list (combo) box. I'd also like the option to be able to select all the Wards for the borough selected so that they can be used in a chartgraph.
View 2 Replies View RelatedI have a form with a combo box. Is there a way, if a user enters something not one of the current choices, to have that choice added to the list?
View 9 Replies View Related