Populating Sheet With Combinations Of Variables
Jul 11, 2013
I'm attempting to build a large (let's be honest for a second this is going to be enormous) spreadsheet for a project I'm working on and TBH I'd rather not do it by hand. With the scale of numbers I'm working with I'll miss something and it'll take forever.
Here's the deal:
I have 5 variables. They must add up to 100. I need to populate their area with all combinations/permutations of said variables in steps of 10. I'd like to do it in steps of 5 but I'm afraid that'd be too large.
Like so:
A
B
C
D
E
Sum
100
0
0
0
0
100
[Code] ....
It's been a while since I was in a math class dealing with combinations and permutations, so one of my questions is, how exactly would I phrase this mathematically?
View 3 Replies
ADVERTISEMENT
Jun 5, 2013
The attached table mimics the massive table that I am working with.
I wanted to populate values of two variables.
For e.g., I wanted to populate the yellow portion of the table with the value "A"; blue portion with the value "B"; Green portion with the value "3/1/1993" and so on (different colors were used to aid my question only, there are no such colors in the actual table).
I have about 700 such portions to be fill in.
My approach: I just started to copy and paste manually which is tedious.
Question: Would there be any automated way to do this?
View 4 Replies
View Related
Jun 20, 2006
I need to create a macro, where the contents of a particular cell are dependent upon summing values based on a word desription from another column. For instance, I would like cell E10 to include a numerical value from cell D5, but ONLY if cell C5 is populated with the word "trust" as opposed to "equity". Conversely, I would also have a macro in, say, cell E11 that would do the same for the word "equity" in column C. I would like the macro to hold for the entirety of different words in column C.
View 6 Replies
View Related
Mar 14, 2013
i'm having in excel- I'm looking to populate rows in 2nd tab based on the data entered in the 1st tab. The rows to be populated in the 2nd tab resides in the 3rd tab. So a match has to be made on the data entered in the 1st tab. When a match is found on the 3rd tab then the corresponding rows from the 3rd tab should be listed on the 2nd tab.
I have an example.xls attached for clarity.
example.xls
View 4 Replies
View Related
Dec 14, 2012
Below is the code for completing Sheet 1 using various comboxes on a User form. There are only 15 lines to which values can be entered on sheet 1. After the 15 lines have been completed, I would like to begin adding new data to sheet 2. However, I'm not really sure where to begin in terms of setting up a counter and then calling sheet 2 once the counter hits my limit. Sheet 2 is exactly identical to sheet 1 in layout and also has 15 lines.
Private Sub btnAdd_Click()
' This button will add medication info to excel worksheet
'RowCount will help find next empty row
[Code]....
View 1 Replies
View Related
Dec 15, 2008
I have 25 random numbers and I would like to get a possible 5 digit combinations of these numbers. Can anybody help me with the possible formula?
View 9 Replies
View Related
Jul 28, 2013
I have a master list. I want to create an excel sheet that will follows when I key in the code number according to the master list all the rest of the value in the master list will automatically populate accordingly into their respective sequence into the created excel sheet.
Eg.
Master list
A B C D
0001 Lucy 8 Walton primary
0002 Rachel 9 Dermouth primary
The created excel sheet
Code 0001
Name Lucy
Age 8
School Walton Primary
I want to key in the code 0001 and the rest of the value will auto populate from the master list into the created excel sheet. The master list data go in accordance row by row. That means the master list is listed in a sequence where all the related value for the particular code into one row only.
View 14 Replies
View Related
Nov 6, 2012
I have an ActiveX Combo box - and Im trying to fill from a 2nd sheet (same workbook). I can get the box to work if the data is on the same sheet but what to I put in the ListFillRange to get it to populate from another sheet ?
View 2 Replies
View Related
Oct 29, 2008
I am trying to total up by a Range of Dates some information from one page to another through VBA. I am having a tough time with this one, I can't seem to get the correct information over to the other page.
I have successfully been able to transfer the data via a series of SUMIF statements, but it really is very inefficient and takes a while when the data repopulated by another program. So I am trying to do it via VBA.
View 5 Replies
View Related
Mar 1, 2007
I am trying to populate a combobox located in sheet1 with data from sheet2.
View 3 Replies
View Related
Jun 22, 2012
I have a workbook with two sheets. Sheet 1 has raw data that contains contacts with multiple statuses. I need to pull out only the contacts that have a positive status and put them into Sheet 2.
I would like there to be no spaces between the contacts in Sheet 2. I'd also need all of the data pertaining to each positive contact (so not just their name, but all of the data in the row that relates to the contact).
View 3 Replies
View Related
Jun 30, 2008
What I am looking for is to select between 7 and 15 numbers in total, I want all the possible 6 digit combinations for this.
EG: if I choose 2,9,11,13,15,17&26, it would look something like this
2,9,11,13,15,17
2,9,11,13,15,26
9,11,13,15,17,26
And so on.
If I chose more numbers (10) 1,2,3,4,3,6,7,8,9,10 it would start something like this
1,2,3,4,5,6
1,2,3,4,5,7
1,2,3,4,5,8
1,2,3,4,5,9
1,2,3,4,5,10
And so on.
Please remenber I would like to be able to secelt between 7 and 15 number and be given all the possible combinations.
I would like it to be in one sheet but if that can not be done on as many as it takes.
It would be good if I could just type the required number into A1,B1,C1 and so on and they just gave the combinations required.
View 9 Replies
View Related
Aug 28, 2013
I am trying to auto-populate a table with data looked up from another sheet. The functions I have used are: Data Validation, vlookup, ISerror and if functions.
Cell B4 in sheet2 uses Data Validation to pick data from sheet1 (in this case "tax") and auto populate the table with the information- Job type, Name, employee type and Job title. If i change from Tax to HR, the table should get auto-populated with the correct information.
I have setup vlookups but I am unable to get it working. I am a novice at using vlookups (I learn it just 2 days ago) and am struggling to get it work. I am not even sure if vlookup is the right tool to get this job done.
View 12 Replies
View Related
Jan 14, 2010
I need help to create a formula that would grab data from one sheet and populate another sheet based on the employee that is selected from a drop down list.
Attached is a draft of what I am looking to do.
Basically this would allow us to enter employee variances from several employees on one sheet and get a detailed break down of their history on another sheet.
View 8 Replies
View Related
Apr 22, 2013
I have a workbook that contains data on a different sheet that I would like to populate on a different sheet by selecting a value from a drop down list. The format of the data is identical just different numbers. But are broken out into different projects... I am incredibly new at this and just barely learned how to make a drop down list.
View 7 Replies
View Related
Aug 5, 2014
I am trying to save 'double keying' with a template I use for event RSVP's.
Basically I have one sheet that has a list of invitees for a particular event (can be anything from 5 to 500) along with a bunch of data about them (e.g. address, firm, dietary requirements etc.)
I want to have a SEPARATE sheet that auto-populates its rows with the name and only some of the other data related to each person that accepts the invitation. Sort of like collating the guest list. If at some point they accept and then decline, I need them to be automatically removed (and vice-versa).
I have attached a simplified version of my spreadsheet for reference.
Event Summary Template v1.xlsx
View 1 Replies
View Related
Apr 26, 2013
I want to create a sort of complex logging system for my work.
As of right now i use a simple temple. Column A has the job number, column B has drawing numbers. Each cell in column B has multiple drawings. So for example cell B2 might contain 101, 102, 103, 104 and so on or something like 101...104. Depending on the situation. The ... means drawings 101 thru 104. What i want to do is have a second sheet that has Column A be the job number but column B has just one drawing #, so an example would be . A1 = 10939 and B1 = 105, 107, 109..111. Sheet 2 would then have A1 = 10939, B1 = 105, A2 = 10939, B2 = 107, A3 = 10939, B3 = 109, A4 = 10939, B4 = 110, and so on. Is something like this possible.
View 4 Replies
View Related
Mar 21, 2014
I'm looking to populate tables for specific tasks that my site performs and compare their performance against the other top sites in the company. I need to pull the site # and their performance based on the task, ranking them from first to last.
View 5 Replies
View Related
Jul 28, 2009
I've been trying for some time now to figure out how to have one sheet automatically fill in another based on a larger data sheet. I'm trying to find a way to take a monthly calendar which contains a row for each employee and column for each day and use that to create smaller lists on another sheet. Basically if someone puts in for a day off, or has a medical appointment etc, that person has a coded single letter for that day for tracking and planning purposes. I want to have a second sheet that references those codes and makes individual lists (the following people are on vacation, these people have a medical appointment etc.)
View 3 Replies
View Related
Oct 23, 2013
(EXCEL 2003)I have 6 worksheets for 6 separate ad reps that will be populated with customer info. To make my life simple I just want column A from each of the 6 worksheets to show up on a master sheet in column A. But what also needs to happen is if we add and delete customers that also needs to happen on the master. I understand the "=" and then pointing to a cell in another sheet and hitting enter. I got that to work but I just have soooo many cells to work with that would just be way too time consuming.
View 2 Replies
View Related
Apr 26, 2014
I'm trying to auto populate a calender style sheet in Excel 2010 based on data from a Work Schedule sheet. The work schedule sheet contains a list of jobs, with each row representing a different job. There is a column for the start date (e.g. 25/04/14) and a column for the end date (e.g. 26/04/14). There are other columns which select resources such as people and vehicles. Each resource may appear on any one of several columns for each row, e.g. Site Operative 1, Site Operative 2 etc.
On the calendar sheet, in which one cell represents one day (e.g. 25/04/14), all the dates are shown along the top going right and all the resources are shown on the left going down.
On the calender sheet, in every cell I want a formula that will look at the Work Schedule sheet and see if that particular resource is being used on that particular date. If it does, the cell can display information from another cell such as the job number or job name to which the resource is assigned; if it doesn't, the resource isn't being used so it can display "Free" or "Available".
View 2 Replies
View Related
May 15, 2008
I have not had to do too many complicated formulas or macros but i could definately use some help on this. If it would be easier to email this workbook to someone, i am more than willing... please be gentle...
I am trying to create a worksheet in my workbook that lists all of the rows from several sheets with the following variables.
1. data from the worksheets named January through December
2. containing a dollar value greater than $0.00 in columns F or G
3. and column I is blank
To display on a worksheet named "Backlog"
It would be pasting this data to A6
View 9 Replies
View Related
May 17, 2013
So far my line of code is:
Sheets(" & Cstr(y) & " - " & Cstr(i) & " load" & ").Select"
Selecting a worksheet with 2 variables in it and some text,where y and i are integer variables. P
View 1 Replies
View Related
Jun 12, 2008
I'm trying to copy multiple cells on the same row, with the row number to be copied stored in a variable (x). Right now i've been trying to use the Range method, but this copies all of the values between "A" & (x) and "H" & (x). I only want the values of the cells in "A", "F" and "H".
(copy_sht) is the name of the sheet to be copied from
(paste_sht) is the name of the sheet to paste into
(copy_row) is the name of the row to be copied from
(paste_row) is the name of the row to be copied to
(x) and (y) - I'm not sure f these are necessary, I've followed a lot of examples to get here, so i'm a bit confused.
Function copyrow(row As Long, copy_row As Long, paste_sht As String, copy_sht As String) As Boolean
'Is passed the names of the sheets, the rows, and copies the data
Dim x As Long
Dim y As Long
x = copy_row
y = row
Sheets(copy_sht).Select
Sheets(copy_sht).Range("A" & x, "F" & x, "H" & x).Copy
Sheets(paste_sht).Select
Sheets(paste_sht).row(y).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Function
To recap, this code will copy all the cells between A(x) and H(x), but i only want the values of A(x), F(x), and H(x).
View 3 Replies
View Related
Jan 12, 2007
I have a blank log template on one sheet, On this I need to be able to enter two variables that tell other cells to pick info from the next sheet. The two variables being 1) Date and 2) DAY SHIFT or NIGHT SHIFT.
On the next sheet is an archive of data running in columns with the dates in row1 and the denoted shift (day or night) in row 2. The rest of the relevant info is in the columns below each date and shift
What formula would I use so that the log template cells fill with data from the archive when the date cell and shift cell data is entered.
View 9 Replies
View Related
Jan 18, 2008
I'm trying to use vba open a new workbook and then edit it. But i can not select cell in the new workbook. but it can be activated!
it can open the file (fname) and activate the sheets (sName), but will flage an error on range select.
Workbooks.Open fileName:=fPath & "" & fName
Workbooks(fName).Sheets(sName).Activate
Range("A8").Select
View 9 Replies
View Related
Nov 20, 2012
I have a query about using Array Variables in excel VBA. I have a set of lookup tables and a main data table. The data table will be downloaded everyday. I need to replace the ID's in the main table with actual data from the Lookup tables.
e.g.
Main Data Table
Color Operator
005--325 005
004--326 004
003--327
001--328
002--322
Lookup Color
001 - Red
002 - Blue
003 - Green
004 - Violet
005 - White
Lookup Operator Table
323 - Operator 1
324 - Operator 2
325 - Operator 3
326 - Operator 4
327 - Operator 5
328 - Operator 6
I have a huge amount of data in the main table. So I can't use a lookup formula for automation. Rather I would like to use VBA to create array variables, scan and copy the lookup data into the array and replace the ID's in the main table.
Only that I am unable to achieve this using arrays. I am very basic in executing code related to Arrays.
View 2 Replies
View Related
Feb 5, 2009
Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...
I have the
View 2 Replies
View Related
May 13, 2009
I have some buttons in different sheets in an excel file, each button has its own code, that is the reason I can not move the code related to each object to another location (sheet or module).
And I have one piece of code in Module1 (Auto_load) in order to execute automatically this routine every time file is opened. Inside "auto_load" routine I initialize some values of some check buttons,options buttons and positions of some objects in diferent sheets, but I can not pass the value of variables between Module and Sheet's code even when I declare as public variables and/or function.
I have the following structure: ...
View 11 Replies
View Related
Jul 27, 2006
Can a Function give two or more output variables. e.g.
Sub a()
x = 5
result = Y(x)
End Sub
Function Y (x As Integer) As Integer
Dim B
B = ... * x
Y = ... * B
this will give back Y as a result. But if I want to get 2 or more output variables (let's say I need to get also B into sub) from one function, how should I do that?
I need this because function works with large matrix and I want to extract some values appeared in between.
View 2 Replies
View Related
