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
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____
I would like to have a sheet where there is a cell designated as "start number" (where any number may be entered) and a cell designated as "how many times". I would then like to be able to click a button and excel generates a list of sequential numbers starting from the number designated in the "start number" cell as many times as is designated in the "how many times" cell.
View 2 Replies View Relatedi have been working on 2 sheets receiving and selling item and QTY, or in other words i have a Stock IN sheet and a Stock Out sheet
is there some sort of a formula which links 2 cells, columns, together in a way that, if one cell's number increased the other would decrease automatically ?
say iv set 50 in cell a1, i want to set 10 elsewhere and the 50 in cell a1 will automatically decrease from to 40
here's my excel worksheets i uploaded it,
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?
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
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 Relatedhow 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 Relatedi 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.
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 RelatedAttached 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.
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)
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?
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.
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.
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.
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 Related1. 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.
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 RelatedIn 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?
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?
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")
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
I have to pay 190000 $ in 180 months. I need a formula to establish an incresing rate for every month. I have a simulation on a paperwork, but I can't figure out the formula. On the paperwork the first rate in the first month is 875,21 $, the second one is 876.99 $, ..., and the last one in the last month is 1258,97 $.
View 5 Replies View RelatedI'm just fiddling around with excel at the moment and have a created a cell with a value of 16. It represents the number of days an event has been running for.
I'm not sure how to make it increase by +1 every day without me having to open excel and change the value in the cell.
in a1 I have a number 1, a2 is a number 2. I want to keep on counting down to number 7691. Just do not know how to do this.
View 5 Replies View RelatedI have a worksheet that I need a piece of code for, Cell E1 is an amendment number that increases every time a new one is put out,
so they can be tracked.
Every time the sheet is saved the value in cell E1 needs to increase by 1.
I am doing a hlookup on a range (possibly over 100 rows). My formula is working but my problem is that it takes too much time to go and manually edit the formula to change the row index number and increase it by one. For example the row index number has to change in order to pull in the right data....eg 5,6,7,8 etc up to over 100. Is there a quick way to do this or do I have to manually enter the row index numbers in over 100 rows?
I am attaching a SAMPLE of data. The actual worksheet is much more larger.
Hlookup.xls
I need a series of cells to increase by varying amounts after each month, however this needs to happen automatically so if I send the spreadsheet to someone else, it will update for them automatically. I'm hoping that I can set up a function that will ONLY increase the cells after 4 weeks after they select a date from ANOTHER drop down menu (i.e they select the starting date from cell A1 - then input their starting numbers into other cells which then increase on that same date, 4 weeks later).
The cells that need to increase already have a formula in them as well; as this number is their predicted 1RM based off formulas in other cells.
I will attach a screen shot when able.
My wife and I run an apartment complex with 2 employees, a manager and a maintenance man.
We use excel to produce a paystub for these employees twice a month.
There are a number of variables including incentives for renting apartments, overtime, variable hours in a pay period etc.
We have a separate spreadsheet for each employee and a separate sheet tab for each pay period named for the pay date... e.g. the first paystub had one sheet named "Jan 15"... the next pay period the paystub had 2 sheets: "Jan 31" and "Jan 15". The third paystub had 3 sheets and so on.
The paystubs have a column with the numbers for the pay period and then a column with the year to date sum (for straight pay, over time, federal withholding, social security, medicare, state withholding, etc.)
Right now I edit the formulas for all these sums for each payperiod. e.g. =sum('Feb 15':'Jan 15'!B35) for the third pay period is editted to =sum('Feb 28':'Jan 15'!B35) for the fourth pay period.
I would like to be able to just copy the prior spreadsheet and not have to find and replace values in 11 spreadsheet cells for each pay period.
I have figured out how to get the name of the current spreadsheet to appear in a cell on that sheet using VBA... but not how to get that cell content to be part of the arguments for =sum(. on the formula bar...
