Autofill Index Value Times Var Num Cells
Feb 17, 2010
Attached is a workbook with some sample data. All cells in column A have a unique value that should be applied towards all values in column B corresponding to that cell in column A. For instance, A1 should apply towards all data in B1.
What I initially did is took all the values in column B and put them into a separate worksheet (this information is on sheet 2 of attached workbook) and used the text to columns to separate them into individual cells, but still horizontally.
What I need is for the macro to take all data in one row, copy paste special values transpose (to vertical) into column B of sheet 3. THEN (here's where I can't seem to get it...) I need that corresponding unique value from sheet 1, column A, to be copied and pasted into sheet 3 and autofilled down for the number of values it corresponds with.
View 5 Replies
ADVERTISEMENT
Aug 17, 2009
In one column I need dates and every date has to appear 16 times.
So, I need date 2009-06-03 in 16 rows, in next 16 i need next day - 2009-06-04.
View 5 Replies
View Related
Jul 28, 2014
So I am putting togther a process matrix template for a Value stream analysis. I would use brute for and simple tricks to make this work but I am handing it off in two weeks to a student and I want this to be as strait forward and as easy to use as possible.
________C_______D_______E________F______G___
_____Part#1___Part#2___Part#3___Part#4___Part#5
Drill 1___1_______2________0_______3_______0__
Drill 2___0_______3________0_______1_______1__
Drill 3___0_______0________0_______4_______2__
Lathe 1__0_______1________1_______0_______0__
Lathe 2__1_______4________4_______0_______1__
Lathe 3__0_______0________5_______1_______0__
The first worksheet has a list of about 180 machines running vertically, and enough space for 20-50 part numbers to plugged in running horizontally across the top. The next three worksheets have the machines organized and grouped together by processes. I planned to use an Index-Match such as =INDEX('Individual Machines'!D$6:D$194,MATCH("Drill 1",'Individual Machines'!$C$6:$C$194,0))
The goal would be to get the user manually fill in the first worksheet, and then allow the user to use the vertical autofill function to fill in data on the later worksheets. This would provide a flexible template that can be re-used for different and various product families.
So populate something like this with a downward autofill for the number of times the part# hits a machine.
_______Drill 1___Lathe 2___Lathe 3___
Part#1___1________1________0____
Part#2___2________4________0____
Part#3___0________4________5____
Part#4___3________0________1____
Part#5___0________0________1____
View 2 Replies
View Related
Jul 11, 2014
im trying to look up data by a date and then between times.
I have wrote a formula in C6 but it does seem to work.
On the auto sheet, I need to lookup the date in cell B3 then handled in cell C3 and finally lookup the time in cell B6. Once I have this I need to look on the Data Tab in row one for the date then row 8 for the handled text and then finally I need it to look at column G to bring back the BETWEEN times.
View 3 Replies
View Related
May 23, 2014
We know how INDEX/MATCH works, and it's very nice. I attached a COUNTIF to it to count how many times the index finds itself on another table; if it doesn't find itself, then it goes blank. However, this time I need to count how many times it finds a certain string condition in the other table.
sampleexcelhelp.xlsx
The columns that need to be filled are shaded in dark pink.
View 5 Replies
View Related
Mar 21, 2007
I have got the task of doing the results for a local athletics mid-distance event once again and im fed up of doing it the present way. Basically i have a sheet which i input the race registrations which has the following collums: Number, Name,School,
and i have another sheet for results which have: Position,Number,Time. I then have to merge the two in a magical way and i get the results. However what i want to do is have the first sheet the same but the results sheet would be different reading: Postion,Number,Name,School, Time
But when i input the number, it will check with the other sheet and automatically fill the rest of the boxes (bar position and time) hopefully. Using my knowlegde i gathered at college(fairly average knowledge of vb) i still cant get it to work the way i planned. Is it actually possible to do, or should i go to my pet hate access?
View 2 Replies
View Related
Dec 8, 2006
I have this code present, however the autofill part needs to work by looking at the cells to the right & i dont know how to do this
Sub Merge()
Application.ScreenUpdating = False
Columns("A:A").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight
Range("A10").Select
ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[1])=TRUE,"""",RC[1]&RC[2])"
Selection.AutoFill Destination:=Range(Selection, Selection.Offset(0, -1).End(xlDown).Offset(0, 1))
Columns("A:A").Select
Columns("A:A").EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub
View 6 Replies
View Related
Jun 11, 2014
I'm trying to find a formula that increase a cell by 1/3 that will work with auto fill.
For Example if auto-filling a column:
3
3
3
4
4
4
5
5
5
Or if auto-filling a row:
3 3 3 4 4 4 5 5 5
View 11 Replies
View Related
Jul 8, 2009
I have a column of names with some spaces e.g. A1: BROWN, A2:empty, A3:empty, A4:SMITH, A5: empty, A6:JONES etc. All the empty cells have the same name as the one above i.e. A2 and A3 should also read "BROWN". A5 should read "SMITH" and so on down the column. Is there away to create a formula that does this automatically. I've tried a simple if command in a duplicate column which looks at the first column and tests for a blank - if it finds one it enters the name from the cell above - but this only works once and some of the empty spaces need to be filled more than once e.g. A2 & A3 above.
View 2 Replies
View Related
Dec 15, 2009
how to find the next empty cell and then copy the data down, however; I am trying to do almost the opposite. I need to be able to click on a cell and have that cell's data copied above it until there isn't another empty cell. I will attach a small portion of my worksheet for viewing. Column A has account numbers and Column B has the brand of product. Unfortunately when I imported this data into excel from our software program it lists all the brands and then the account number is listed on the last row of brands.
View 3 Replies
View Related
Apr 7, 2008
i am trying to use a small piece of code to find the last row in an excel sheet, to copy/paste formulas and data.
Dim LastRow As Long
If WorksheetFunction. CountA( Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
End If
i am using this code like this:........................
if i filter only the DD the formula will find the last row being row 5, and the formula will fill all the cells untill that row, including 3 and 4. Is there any chance i can autofill only the active cells or filtered cells.. i don't know.
View 2 Replies
View Related
Jan 7, 2009
Is it possible to change the Autofill setting from 'fill series' to 'copy cells' in Excel 2003? Otherwise I have to keep changing it after each autofill, which is an additional two mouse clicks ...
View 7 Replies
View Related
Sep 15, 2013
I have data in a row - A1 B1 C1 D1 etc.
And a SUM function in row 2 - =SUM(A1:C1)
I want to be able to drag this function to calculate SUM in blocks of 3 e.g.
=SUM(A1:C1), =SUM(D1:F1), =SUM(G1:I1)
View 7 Replies
View Related
May 22, 2014
To be more specific, I work with highway signs, and they all have codes. the codes are priced according to their size, sheeting, and aluminum structure.
so codes can be something like : GS-4, IB-12, RB-21, and their sheeting could be: 231, 241, 245S, 420P, and their price is dependent with the size, materials, and quantity ordered. So a quantity of 3 or less of a flat sheet of aluminum, with HIP reflectivity sign would be a 231, where 4 or more of that same sign would be 241.
A workbook has the contracted price, the cells are in order. The order sheet I use is separate from this. But I have to look for each sign for all the info. How can I get the one cell to recognize the Code, along with the Quantity ordered?
View 2 Replies
View Related
Feb 9, 2014
I have a workbook with two sheets. The first one, let's call it the Main sheet, will be used to keep track of the touring artists of a record label. The second sheet contains a list of show venues with their respective contact info. Here's what the two sheets look like :
Main sheet
mainsheet.jpg
List of contacts
listofcontacts.jpg
What I would like is row D of the Main sheet to autofill, according to the contact information contained in row B of the List of contacts, when I enter a venue name in row C of the Main Sheet.
View 3 Replies
View Related
Jul 14, 2014
What I'd like to do is fill specific cells with specific information drawn from other pages based on a combo box selection.
For example, if I were to pick 10131 in the combo box, I'd like it to fill out cells X, Y and Z with information from the three cells to the right of 10131. Assume X, Y and Z are not touching.
View 1 Replies
View Related
Nov 15, 2007
In file named (Book 1) I have a lots of tabs named e.g. pd1 pd2 pd3 etc.
In another file (Book 2) I need to link cell A1 (on Sheet 1) to cell A1 on pd1 (in Book1)
Which I can do fine.
In Book 1 I want to autofill the formula i have that refers to A1 on Pd1 but when I drag it I want the formula to go up in a series to refer to Pd2, Pd3.
So the final formula looks at $A$1 on Pd1 then on Pd2 Pd3.
View 9 Replies
View Related
Jun 21, 2013
I would like to enter a long line of data (text & numbers) into a single cell and have the single cell data populate successive horizontal data fields with automatically. The single cell data would have properties that would correspond to specific properties in the successive fields. The single cell data would remain unchanged. The successive horizontal cells would be looking for a specific piece of the single cell data.
View 1 Replies
View Related
Jan 27, 2009
1. I need a macro to find a unique number, say a 10 digit number starting with 4100.. and move it 1 row down and 3 rows to the left. It needs to look only in one column (E) for this number. This row contains several unique numbers, variable number of blank cells between them, all having 10 digits and starting with 4100.. - So the macro needs to repeat this for every unique number.
2)Once it does this, I need it to autofill the unique number in all the cells in column A until the next unique number is reached.
View 5 Replies
View Related
Jul 7, 2013
I am trying to get two cells to be used to adjust other cells on the same sheet repeated times.
In a inventory sheet I have 5 columns as such , A1 previous balance, B1 Qty received, C1 qty on hand, D1 last cost, E1 current avg cost What I want, is to be able to enter my weekly received items in the B1 Qty received and the new cost in D1 Last cost cells and have them calculate my current average E1 and update my qty on hand C1 total. This in itself is not the biggest challenge. I was asking if there is a way that after the E1 current average cost and C1 qty on hand are updated by that formula, that the next time I enter a new B1 qty received and new cost in D1 Last cost cell they will update again basically without changing the earlier calculations achieved .
Example: Today I have item X with a A1 previous balance of 10 , with a D1 last cost of $1, and E1 current avg cost $1
I want to receive B1 10 more today at $.50 D1 last cost, which ideally would end up showing
A1 previous 10 (or 20 if adding) , C1 Qty on hand 20, D1 Last cost .5, E1 current avg $.75
Next week I want to receive B1 10 more at $.25 D1 last cost , which then would update showing
A1 previous balance 10 (or 30 if adding), Qty on hand 30, Last cost $.25 and E1 current avg $.58
View 4 Replies
View Related
Feb 10, 2009
I am trying to get a function (sum) of several cells to be automated in a monthly spreadsheet. I can get the function to work, but how do I get it to Autofil? The syntex seems to call for a range, which will be different every month. I can't figure out how to loop it, so I thought autofill would work.
View 3 Replies
View Related
Jan 23, 2014
In earlier versions if I used the double clicked the autofill handle it would only fill down to the next adjacent cell containing data. Now it fills down to the bottom of all the data regardless of whether there is a blank cell or not e.g
X X X Z
X X X Z
X X X Z
X X
X X X
Now this happens
X X X Z
X X X Z
X X X Z
X X ...Z
X X X Z
How can I stop this as I need to enter different data in the next section?
View 1 Replies
View Related
Jul 30, 2009
Copy and paste the formula into a range or paste a formula into the first cell in the range and autofill down for remainder of cells?
P.S. when autofilling down, can I specify somehow for it to stop at the last row with data in adjacent cell?
View 9 Replies
View Related
Apr 19, 2006
I've created a User Form in VBA. When this is filled out, and they hit the enter button, I want the info they entered to automatically be entered into a new worksheet within the document.
Here's the code I currently have:
Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheet("Sheet1")
View 5 Replies
View Related
Sep 20, 2013
I am currently trying to make a digital time card for my place of employment. I has an odd set up and odd date ranges. The pay period starts on the 21st of each month and ends on the 20th of the next month.
The time cards have 5 columns for each week starting on Mondays going to Sunday (also weird its not Saturday to Sunday) What I was hoping to accomplish was being able to select the starting month of your choice for pay period and have multiple cells update the week date range.
Date
Date
Date
Date
Date
**Here would be the drop down month select.
Sept 21 - Sept 22
Sept 23 - Sept 29
Sept 30 - Oct 6
Oct 7 - Oct 13
Oct 14 -Oct 20
View 2 Replies
View Related
Feb 8, 2008
I have reviewed many a thread today looking up how to color in cells c11 through c20 without conditional formating.
if these cells have a time less than 01:00 then they turn green
if these cells have a time greater than 01:00 then they turn red
if the time is equal to 01:00 then they turn white.
View 9 Replies
View Related
Nov 20, 2009
I am trying to add a ROW of times. Each cell of times is using the 00":"00 format so I don't have to type a colon between the hours:minutes.
I'm not sure of what the formula should be for the Total Hours box.
For example, if I were to type 13:45 in the start time & 14:00 in the end time, I am getting a result of 0:55 which is incorrect
Another example if I leave the first 2 start/end boxes empty(blank) and then type 15:30 Start & 17:00 in the second set of Start/end boxes, I get a Total Hours of 1:70 which is also incorrect.
The formula I have in the Total Hours box is =SUM(B12-A12)+(D12-C12) which is know is incorrect.
What is the correct formula which will give me a Total Hours which adds a row of times correctly?
StartEndStartEndTotal Hours
View 9 Replies
View Related
Sep 9, 2006
Is it possible to create an individual variable time clock that is only useable by a specific workbook?
I have set up a calendar which has all days of the week in column 1, with start times in column 2 and end times in column 3. These times are adjustable on a daily basis.
Is it possible to create a macro that reads through the sheet and tells the workbook to disregard the system clock and use only the start and end times for each date stipulated in the table.
ie 7/9/06 has a start time of 6am and a finish of 6pm, not 0000 and 2400 as it would with the system clock?
View 6 Replies
View Related
Jun 19, 2014
I want 2 cells that are not next to each other and that contain times of day to highlight only if the times overlap, I cant figure out how to make the conditional formatting do this...
View 2 Replies
View Related
May 1, 2007
ok is there a way to have excel fill a certain number of cells with specific data specified X times? For example, the end result would look something like the pic below. The column on the right would change according to the numbers specified in the yellow column.
View 9 Replies
View Related
