I need an action to allow for the random selection of a predetermined series of number codes which represent a given letter.
It is probably easier to look at the attached sheet and the previous discussion to see what I mean.
Previous discussion:
http://www.excelforum.com/excel-work...-of-cells.html
If a user selects certain options from a drop down list created by cell verification (from list), is it possible to display an input box and have the resulting input populate on another worksheet in the workbook? I have an attendance template I'm working with and if a user selects OT (Over Time) then a input box is displayed prompting for how many hours. The overtime is then tracked on another worksheet.
View 1 Replies View Related I have been able to put together the below which generates a random 15% sample of all records in the worksheet.
It works perfectly, however what I would ideally like to do is get it to only produce the 15% sample of rows where it is todays date in col B and has the word "other" in col C. I've tried lots of different IF statements and FOR statements, but am only generating blank worksheets.
Sub getrandomrows()
Dim lr As Long, lc As Integer, a As Variant, MyDate As Date
lr = Cells.Find("*", LookIn:=xlValues, after:=[a1], searchorder:=xlByRows, _
[Code].....
Create a random selection based on odds. It would be similar to the RANDBETWEEN function, but in this case I specifically want one of the three values to be selected randomly in cell C2 based on the odds given.
View 9 Replies View RelatedI am working on a challenging project. I have a list of names, job titles, and departments. Is it possible to select a random name who matches a specified job title or department? I'd prefer not to sort/filter/delete the original data range because the sheet will be used multiple times during the day by multiple users and with different criteria each time.
View 6 Replies View Relatedsee the attached below.
A few things to sort if I may
How do I get the listboxes to pre-populate based on the Userid and Option Button selection? Furthermore, write back to the correct cell once the selection has been made?
How can I get the combobox to remove blanks in the Userid? (if each cell in row2 has to be populated then so be it)
ToolMatrix3.xlsm
I have a list similar to the below:
#....|Name.... | Weighting %
1Artur Boruc 1
2Igor Tudor 5
3Alessandro Grandoni 3
4Gian Elia Amoretti 3
5Olivier Kapo 12
6Kim Källström 13
7Emiliano Bigica 13
8Julio Baldivieso 12
9Fabrizio Miccoli 13
10Peter Vougt 14
11Massimiliano Esposito 12
What I want to do is randomly select either number 1 to 11 (leftmost column OR the person it represents) but also make the selection based on the percentages in the rightmost column. To illustrate using the above example, Number 11 (Massimiliano Esposito) should have 12 times more chance of being randomly selected than Number 1 (Artur Boruc)...
I have used a formula such as
PHP
=INDEX(A$1:A$11,INT((RAND()*11)+1),1)
to randomly select a cell but how would I modify this to include cell weightings? Or is there a better formula?
An additional note I have is that the weighting for each person will always be changing, but will always be a percentage.
I have a input selection, when i keyin XYZ, the data under column XYZ should be ranked in ascending order. If i select ABC then it should rank ABC data in ascending order.
XYZ
ABC
Input--->XYZ
XYZABC
15
38
26
42
Rank the column by selectionXYZABC
I have a spreadsheet where the start date is manually entered in Cell B1 and an end date is calculated in Cell B3. I would like to have the dates between the start and end date, with a step value of 7 days show in the rows under Cell B3. How do I get this to work?
View 3 Replies View RelatedI have a spreadsheet which contains our fleet information covering many vehicles in one list. The teams for these vehicles are listed in Column A. What I would like to have is:
A dropdown box to pick from All, or the various teams in A1To hide every row that doesn't match the selection of A1To reveal every row that contains the selection for A1 at any point in column A. e.g: "*Dog Team*"
This is because something might be listed as "Moved from Dog Team" or "Dog Team, temporarily on loan", or "Dog Team V66.m"
I have taken a small sample of the data I will be working from and made a very simple drop down box which displays the pages of information for the vehicles by hiding/unhiding blocks of columns:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
If Range("A2") = "MOT/Service" Then
ActiveSheet.Columns("A:P").EntireColumn.Hidden = False
ActiveSheet.Columns("Q:CE").EntireColumn.Hidden = True
ElseIf Range("A2") = "Contact Details" Then
[Code]....
But because there is also filters on the sheet, meaning the positions of the entries based on rows can change, I need it to search for the "*xyzzy*" method, but am unsure how to do this! While just using the Filters will work, due to some human limitations I have been asked to have a specific drop down box in a very specific location with instructions next to it.
The first line of the code chooses the columns to select; all columns until there is no value. From there I need to have it sort those columns based on row 1. The problem is that the columns chosen are variable. It could be columns I:N (as shown below) or column G:Z or any other combination. (The code below was recorded if that matters at all.)
View 2 Replies View RelatedI want to set up a cell so that when the cursor is on the cell a drop down option appears allowing the selection of a yes or no to be shown in the cell. What's this called and how do I set it up?
View 3 Replies View RelatedI have a group of 3 Option Buttons that are grouped by a Group Box. I added another Option Button and placed it in the third position, moving the button that was originally third to the bottom. When I select the fourth button (that was originally the third of three) it still returns a 3 in the linked cell, and when the new button (placed in the third position) is selected, the linked cell returns a 4. How do I change the order that the linked cell sees? In the attached spreadsheet, the Group Box in question is the Pricing Method on the Tow worksheet.
RateCalcApp.xlsm
I have a workbook with 2 sheets. Sheet1 has a textbox and 2 option buttons named "north"and "south", and 2 command buttons named "send"and "retrieve".
Also a lot of other boxes, but they're not relevant.
I enter a unique number in the textbox, press one of the option buttons and press "send".
All data is now written in sheet2 in one row.
this all works ok.
Sometimes I need to change some data, so I enter the number in the textbox and press "retrieve". I get all the data back on sheet1, except the option buttons don't change correct. If on sheet2 the value = "north" I would like optionbutton1 to have the black dot and if the value="south" optionbutton2 should have the dot.
Now it's like they are on strike. they just don't react folowing the value of sheet2,collumn G, row "x".
I have done much searching, found some relevant, similar postings, but I have not been able to make it work for my code. I want to have multiple option buttons and have a macro determine which one is selected. One similar posting had macros run from the clicking on the option button, but I want the macro to determine which is clicked and then return some functions. From my research...this is my code.
Sub temp()
If OptionButton1.Value = True Then
Range("C1").Select
Selection = "OB1"
Else 'assuming two buttons and first is false
Range("C1").Select
Selection = "OB2"
End If
End Sub
This gives me an "Object Required" error on the first line of the code.
First time poster, long time viewer for help.
this one should be an easy one for you experts.
i am in charge of making an assignment list in my classroom .
cells A1 all the way to A20 have a single childs name in them.
in the corresponding box ,B1 which is next to the first name and B2 next to name 2 , etc etc. i would like to put 3 random chores they need to do for the week. the first chore being in column B the second chore in C and thirs chore in D . in other words, each child listed in column A , 1-20 will have 3 chores to do over the week .
all 3 chores for each child will be in the same numbered row as their name.
i have a list of all the chores at the bottom of the spreadsheet, theyre from A20 to A40 (20 chores in a row) . i want excel to randomly put A20-A40 in the corresponding boxes next to the kids names.
im tired of having to mix all their chores up for the week next to their names.
I would like a VBA code that will display a large input box with multiple inputs boxes inside.
I have shown below the variables required (column C) and the default values (Column E). There is a combination of dates, percentage and numeric values....
I have an inputbox in my code. By default the inputbox is giving the options "OK" and "Cancel". Is there a way where I can have only "OK" option in my inputbox.
View 3 Replies View RelatedI have data for number of firms as :
year firm
1 1
2 1
3 1
1 2
2 2
3 2
1 3
2 3
3 3
.............
.............
1 1000
2 1000
3 1000
how can one chose random samlpe from this population.
I have a file (see SAMPLE file attached) with over 2000 records. I need to know if there is a way to do a sampling or random selection by ethnicity and gender in order to get a diverse population for only 60 individuals. The selection criteria should be on ethinicity and gender.
Is this possible?
random number selection...
i have a list of 200 items (rows) and i would like to "randomly" select 30 of them...(next month i will get another 200...)
I have a userform that has 3 option buttons (1, 2 & 3) each of these option buttons have a Label next to them, Label (1, 2,& 3) next to that I have 3 textboxes that are formatted for currency. Each label represents a dollar value $10.00, $20.00 & $30.00
OptionButtom Label TextBox
1 Month Member x $10.00 $.00
2 Month Member x $20.00 $.00
3 Month Member x$30.00 $.00
When one of the OptionButtons is selected I want the corresponding label dollar value to be multiplied by the OptionButton and that value placed in the TextBox
i.e. if OptionButton 1 is select the TextBox would have a value of $10.00
if OptionButton 2 is selected then TextBox would have a value of $40.00
if OptionButton 3 is selected then TextBox would have a value of $90.00
Lastly, if 1 Option Button is selected and then changed to another of the 2 buttons, the TextBox value would reset to $.00
I'm trying to select an x number of random selections within the selection.
Basically, lets say A1:A100 is selected, im going to have a inputbox asking how many to select, you put 10 in the inputbox, and then it randomly chooses 10 from A1:A100 and highlights them
This seems easy enough, i can do the input box and know the random function. just not sure how to set the beginning point and the ending point with Cell locations and then have it highlight those selections... a for loop I'm guessing?
I have a set of 20 numbers that I regularly wish to select 6 numbers from, without duplicates
View 9 Replies View Relatedorganising a golf tour for 32 players.playing in teams of 4 (4 Balls) for 4 games of golf and the teams are to be randomly selected such that no player plays in a group with a person he has played with in a previous game. finding it difficult to create a spreadsheet which meets this criteria.
need to build into the spreadsheet the fact that the variable mix of people is constrained by the fact that at least 4 persons have to use a buggy (which will have 2 people in it) and as the tour progresses and courses become physically demanding a number of people (N) for the last 2 rounds may also ask for the use of buggies as well.develop a spreadsheet to make the 4 Ball team selections.
I want to do is randomly pull from a field of data, MINUS any cells that do not meet the criteria in other columns. So if column A is my target column for the random selection (containing 10 unique entries), and column B has 10 repeating colors, and column C has 10 repeating animals, what can I use to perhaps create this effect: A random pull from column A, but only from rows in which column B says red and column C says cat? (The pull being 1, 4, 7, or 9 in the below example, of course.)
Data (this example is also attached in an excel doc):
One Red Cat
Two White Dog
Three Blue Horse
Four Red Cat
Five White Cat
Six Blue Cat
Seven Red Cat
Eight Red Dog
Nine Red Cat
Ten Red Horse
I have tried variations on IF and INDEX to no avail. Perhaps something with SUMPRODUCT?
I have looked at many different examples of uses of the average function but I haven't found any examples of what I need it to do. Here is the code I am trying to use, but I am getting some errors.
View 6 Replies View RelatedI have set up an option frame with two options. If I click on option A it shows below a frame with txtLenght and txtWidth.
If I use option button B it shows me on the same spot a different frame with txtSQM. I need that so I could either enter the length and width to get the sqm or I do already have the sqm value.
When I set up the code I would like to use an input message saying "Enter Lenght & Width" but if I use the other option it would need to say. "Enter the sqm value".
How or what code do I need to enter to be able to seperate those two from each other.
But also it is important that it does only calculate either the value in sqm or the values from length and width.
How can data be input through option buttons, textbox, listbox, combos, checkbox in web pages organised in frames?
View 3 Replies View RelatedI am wondering, in excel 2003 is it possible to randomly select two numbers from one column, say column b9:b45, that are random numbers, and selected by two different font colors. In other words: b9=1348, b10=1349, b11=1350, b12=1351, b13=1352, b14=1353.
Say we press a macro button, and in field B50 one radom black colored number is selected of a field of say 30 numbers that are all black in that column, and in b51, one red number is selected, in a field of 30 red numbers. We want to used this macro to select winners simulating a raffle drawing.
