Use Combobox ListFillRange To List Values From Two Groups Of Cells?
Feb 24, 2010
Is it possible to use the Combobox ListFillRange to list values from two groups of cells? For example, in column 'A', I have the values, 1,2,3; and in Column 'B', I have the values, A, B, C. I want to list the values in column 'A' along with the value of say, B, in Column 'B'.
combobox.listfillrange = "Sheet1!A1:A3" < ---- how to include value B from column B in this list? Or, how to get value A from column B?
View 8 Replies
ADVERTISEMENT
Apr 2, 2008
I'm using a Combo Box the contents of which are on another worksheet.
This data is pulled in using a database query(MSQuery). The data source could be 5 lines long or thousands of lines long(Customer dependant).
I have tried specifying the listfillrange using $G5:$H8700 for example which works for the current dataset. If i change the dataset which only has range G5:H87 then the properties of the combobox listfillrange change to $G5:$H87 but the actual list has more than 82 lines and displays the 82 lines over and over again. I haven't counted the number of lines but it could be thousands.
View 8 Replies
View Related
May 5, 2008
When in a Combobox from the Control toolbox toolbar, the data for the Listfillrange are in a column (e.g.: myrange = $A:$A), it works fine. But when the data in myrange are in a row (eg.: myrange is =$1:$1) , the combobox shows nothing!. What can I do to show the combobox all data (A1,B1,C1,D1 - last data?) ?
View 9 Replies
View Related
Mar 10, 2014
I need to distribute a list of data into 10 even groups of data. Here is the scenario.
I have 10 salespeople on my team with a list of accounts that need to be evenly distributed based on two criteria
- Each sales person needs to have the same number of zipcodes , with the closest TOTAL # of accounts and TOTAL $ value as possible
View 6 Replies
View Related
May 7, 2008
How do I set a ComboBox list to read values from an array? I tried the following in VBA, but it didn't work....
ComboBox1.List = Array("January", "February", "March", “April”, “May”, “June”, “July”, “August”, “September”, “October”, “November”, “December”)
View 9 Replies
View Related
Jan 12, 2014
how I can list 3 different values to a list of list of 3 in a combo box, is.
combo box
select high = 35
select middle 30
select low = 25
I have the names in the box I just need it to add a value (which I have listed 1 in each separate cells) to each selection
View 9 Replies
View Related
Sep 29, 2006
I am currently trying to add some functionality to an Excel workbook and I have a combo box that I am unable to get the values to populate. On the same worksheet I have a command button. Here is the code I am using to attempt to populate the combo box:
Private Sub cmdSendSave_Click()
Call SendSave
End Sub
Private Sub bxLocation_Change()
With bxLocation
.AddItem "Mt. Hope"
.AddItem "Summersville"
.AddItem "Huntington"
.AddItem "Pulaski"
.AddItem "Coastal Bend"
.AddItem "Odessa"
.AddItem "Wheeling"
.AddItem "Hollywood"
End With
End Sub
View 9 Replies
View Related
Dec 19, 2006
How can I get a combobox to list the values in a range of cells eg.
A1:A5 = 1,2,3,4,5 etc
I've tried...
ComboBox1.List = Range("A1:A5")
But that dont work (it was more of a guess)
View 9 Replies
View Related
Dec 12, 2012
I have attached a file to better describe my issue, essentially I would like to count all the 'A's, all the 'B's and so on in the file and have the count next to it, so something like
A 6
B 3
D 5
F 1
In the file I uploaded I have column B as a count of the numbers, is there a way I could get just the final number for each letter, that would solve my problem.
sample.xlsx‎
View 4 Replies
View Related
Aug 31, 2007
Is there any way to determine all possible unique combinations (or sets) of any 3 strings in a table? I need to make a master set list from a list of strings which could be over 100 elements long.
Once I have a result set, I need to get rid of any groups which have a value contained in another group.
Example:
cells A1 through B100 contain:
Item1 45
Item2 50
Item3 98
...
Item100 75
Result set would contain groups of any 3 item combinations where the corresponding "B" value is within a certain range (between 40 and 80), while also trying to create sets with the lowest possible sum (placed in the 4th column) of the 3 numbers.
One possible combination would be:
Item1 Item2 Item100 170
etc...
I need to list all possible combinations, without re-using an element from a previous (lower sum) combination that matched the criteria.
View 13 Replies
View Related
Feb 8, 2008
How can i Loop through a combobox's values and compare to a string value and then set the listindex of the combobox to that value?
View 5 Replies
View Related
Dec 4, 2009
I want to take a list of data and then subtotal the value by certain groups eg Toys. I then want to sort those subtotals by value descending. I then want to rank the sub totals
So basically i want to first select the group total and if that subtotal is the highest rank it. Its almost as if I need two subtotals (although Excel only gives me one)
View 4 Replies
View Related
May 21, 2013
I have SEVEN numbers and FOUR groups.
What I would like to do is list ALL the PERMUTATIONS of those SEVEN numbers in FOUR groups.
So basically, the groups would look like...
7000
4210
4201
4111
4003
3400
3310
3301
3301
3220
[code].....
...and EACH group would add up to SEVEN etc.
I think there are a total of 840 different PERMUTATIONS available, from anything from 0 numbers in a group to 7 numbers in a group.
I need ALL these groups listed individually .Is there some code that will do this?
View 4 Replies
View Related
Nov 2, 2011
I have a list of 600 figures (this may vary) but i need a formula that will look a one particular figure in this list and return if it is one of 5 equal groups. e.g in this example the lowest 125 figures in numeric order would be in group 1 up to the highest 125 figures would be in group 5.
I realise if I limit it to 4 groups then i could use the 'quartile' functions but i am stuck with 5 groups.
View 3 Replies
View Related
Nov 30, 2006
I am trying to fill a Listbox dynamically. I have a range labeled 'Dates'. This range is created dynamically when i click a refresh button. New dates are grabbed from a database, then inserted into a worksheet, then the range 'Dates' is created to define the list of dates from the database. Also, I have a Clear Button. This button deletes all the dates in the worksheet, which I would want to clear all of the selections in the Listbox.
My Questions are:
1) How do i dynamically update a listbox with a Dynamic Named Range? I currently have vba to assign the ListFillBox property with the range name. However, when i click refresh to grab a new list of dates, the ListBox object freezes and/or does not refresh with the new dates.
2) How do i clear the listbox. Currently, I have vba to assign ListFillBox = "", however this will freeze the listbox and/or not clear its content.
I am using a multiselect listbox object.
View 8 Replies
View Related
Feb 4, 2009
I have a spreadsheet containing Date, Description and Value in a three column setup.
I wish to generate one summary for each unique description, based on a table of types. I can then say that for instance "Apple", "Oranges", "Lemon" are all part of a "Fruit" group.
The lookup would then find every row containing words in this group and summarize the values for them all.
View 7 Replies
View Related
Aug 24, 2013
I'm trying to make a excel template that will take a list of names, changes but generally around 100, and randomly separate them into 4 sets of 8 groups evenly.
This grouping would be repeated 4 times, but there are some conditions.
Firstly, the same person cannot be put into a group again with someone they have previously been grouped with.
Secondly, someone appearing in the first or last groups cannot appear in that group again.
I've been trying to do it via some complex cell formulas but they are quickly becoming overly complex and im not sure if it will actually work which has completely demotivated me. Ive not been looking at VBA but am now thinking its the right route. Ive been thinking of having a master list and then have an attribute of who people have been grouped with before during each of the groupings, and also what number group they were in and then checking against that or something, but im not sure if that is the most efficient solution.
I attached an example dataset : demo dataset.xlsx‎
View 2 Replies
View Related
Jun 16, 2006
I have a named range that populates a list box. The spreadsheet has a macro that allows additions to the list. The macro will expand the range to include the addition but the List box does not show the update unless I save the workbook and reopen. How can I accomplish this without having to close and reopen?
View 4 Replies
View Related
May 16, 2014
I am trying to improve a spreadsheet that I have to use everyday. I have a list with varying number of entries (different length every day) that has a number of columns including a column that contains machine codes (say 1,2,3,4,5...).
Each machine is programmed by a different person (say Andy,Ben,Craig,etc.).
One programmer can program more than one machine so for instance Andy is interested in programs for machines 1,3,5 and Ben in machines 2 and 4.
Every day we send a list of jobs that require programming which is pre-filtered for each programmer so if I send the list to Andy he only sees rows of data with machine codes 1,3 and 5.
I am trying to make a drop-down where I pick the programmer's name and the list automatically gets filtered for the machines for that specific person (e.g. 1,3,5).
I believe this could be done with advanced filtering but the problem is that the list length changes every day and the data is pasted into the spreadsheet from a different CSV file so I cannot define a set range for advanced filtering because it does not accept blank rows...
I am attaching a screenshot : list.jpg
View 1 Replies
View Related
Feb 2, 2010
Is there a way to do the following using conditional formatting?
I need to have all duplicates highlighted.
For example (Since "Ana M." is the first) highlight both rows (or at least the group of "Ana M." cells in the column, entire row not necessary)
Highlight:
Ana M. 12 A St 333-3333
Ana M. 23 Z St 333-3333
Skip:
Jane S. 12 A St 555-5555
Jane S. 15 Z St 555-5555
Highligt:
Sam A. 55 A St 222-2222
Skip:
Tony J. 45 A St 444-4444
Tony J. 11 B St 444-4444
Tony J. 66 Z St 444-4444
View 9 Replies
View Related
Feb 12, 2010
On Sheet1 I have ListBox1 referencing data from Sheet2 for the ListFillRange property. The Listbox is a control box added on to the worksheet.
I wish to have a VBA Subroutine that defines the ListFillRange dynamically according to the size of the source data range. ie. Sometimes the range is AH3:AL300, othertimes the range could be AH3:AL200 etc. The last row of data needs to be the last selection in the Listbox.
The range looks like:
1 10:00 blah
2 10:05 moreblah
etc
View 9 Replies
View Related
Dec 17, 2013
I have a file with immunophenotypic markers and their relationship to certain blood cells.
What I need to do is search the list and pull out the groups of defining markers for each blood cell. Some of the cells have many markers, some only have one but I need to find which combinations of markers identify each cell if possible.
I have attached the file (I think!)
View 14 Replies
View Related
Feb 27, 2014
Is it possible to separate a range of cells with numerical values into "n" groups that have equal (or as close to equal as possible) total sums?
Ex. The range A1:A30 includes 30 random numbers between 1 and 12 (obviously there are duplicates). I need excel to auto-generate 6 groups of 5 cells each with each group having the same (again, as close as possible) total sum. Preferably, I'd like the numbers in each group to be similar from top to bottom, but I'm not overly concerned about that.
View 2 Replies
View Related
Jan 28, 2007
i'm writing a refrigeration selection user interface, working from values on an excel spreadsheet. how to get the programme to automatically select a value from a list or range once a user has selected corresponding value from a list within a combobox. for example if a user sets the temperature of their refrigerator to -5 celsius i need the programme to automatically select the corresponding value of enthalpy for the air at that temperature.
View 5 Replies
View Related
Sep 2, 2013
I have these kind of matrices (below) and I'd like to identify unique values specific to different groups of columns:
[Code] .........
For instance, in the example above, if we decide that A, B, C are "Group 1" and D, E, F are "Group 2", and that the values in rows 1, 2, 3 are independent (i.e., "x" in "1" is not comparable to "x" in "2", etc):
- In condition (row) 1: "x" is a specific value only found in Group 1
- In condition 1: "y" is a specific value only found in Group 2
- In condition 2: "x" is a value found in majority in Group 1
- In condition 3: no specific value can be associated to Group 1 or 2.
What I would like to get is a measure of whether:
(1) there are values over-represented in one of the groups, or 100% specific to one group.
(2) what are these values
(3) if multiple values are a bit tricky, then: what is the value which is the most over-represented in one group compared to the other (the maximum being 100% in one group and 0% in the other)
View 1 Replies
View Related
Apr 8, 2007
is it possible to perform calculation such as sum,multiply value of 2 combobox and get it shown at 3rd combobox/label at userform initialize stage?
View 9 Replies
View Related
Jun 9, 2006
I have a userform where I have 2 comboboxes. The first combobox shows the the first column (only 1 of each) and the second comboBox shows me the secondary list that correlates to the valuse in the first from column B. Now I have a text box that I am trying to get the value from column C depending on what I have in the first 2 comboboxes. What is the easiest way to do it? This is all in VB since it is a UserForm, and using Vlookup seems to be too many lines if I go that route. Is there a way to use Index and Match in VB where it would be more efficient? I attached just a sample of how the data would be layed out in the Excel sheet.
View 4 Replies
View Related
Jan 23, 2013
I am trying to sum groups of cells where the groups contain a variable number of cells (within a column). My sheet has the following columns, C = plot#, G = plant#(within the plot), and F = number of tillers(for each plant). I need to get the total number of tillers per plot. Plots have different number of plants, so for instance cells c11:c16 are plot 3, cells g11:g16 are number 1 thru 6 (plant#), so I need the total number of tillers which would be sum(f11:f16). But each plot has a different number of plants. I've been told I'm not the best at explaining things,
View 2 Replies
View Related
Mar 14, 2013
I want to calculate the most common text value for groups. I have a table with a lot of records like an example below.
ID Code
1 HB
1 KU
1 KS
1 LV
1 KS
2 LM
2 KS
[Code]....
I want a table like below:
ID Code
1 KU
2 KS
3 KS
How can I do it in Excel 2010?
View 9 Replies
View Related
Feb 7, 2012
im working with a number of related cells and am having trouble with entering formulas. at the moment im entering them in manually but im hoping theres a better way.
suppose I have a few groups of data. the first group is A1-C1, second group is D1-F1, etc. the values of these cells have to be output somewhere else. so if A1=dog, B1 = cat, C1=car then AA1 would be dog cat car. AB1 would be the concatenation of the next group.
Now my problem is how do i put a formula into AB1, AC1 etc. I cant drag and drop. If I drag AA1 to AB1, AB1 will be the concatenation of B1-D1 instead of D1-F1.
As an aside one thing I thought of is to just put the output cells further apart. So if the first output was to goto AA1 the next one could be output to AD1. The only reason I didnt do this is this sheet is wide enough anyway I didnt want to make it any wider. I also thought of outputting D1-F1 into AD1 and then copy/pasting those values into AB1 but that wouldnt work as I need these values to be changeable.
View 1 Replies
View Related
