Autofill: Replicate The Formula Exactly As In The Initial Cell
May 3, 2007
I have two formulas which I'm using: =180*(120.5-1)/119.5 and =90*(60.5-1)/59.5
In the first example, I want to replicate the formula across row 1, but with the value '1' incrementing by 1 each time, upto 240.
In the second example I want the '1' value to also increase by 1 for each cell down column B, up to 120. when I try dragging the formula down or across, I am only able to replicate the formula exactly as in the initial cell (ie. the '1' doesn't change). I've tried using the $ symbol, but this doesn't work. I don't fancy doing this manually for a total of 360 cells!
View 5 Replies
ADVERTISEMENT
Oct 18, 2007
How can I continue to replicate this formula automatically?
Interest RateAmountM1M3Formula Required
1%10000010001000=B2*C2
2%10000020001000=B2*C3
3%10000030001000=B2*C4
4%10000040002000=B3*C5
5%10000050002000=B3*C6
6%10000060002000=B3*C7
7%10000070003000=B4*C8
8%10000080003000=B4*C9
9%10000090003000=B4*C10
10%100000100004000=B5*C11
11%100000110004000=B5*C12
View 9 Replies
View Related
Feb 12, 2008
I have two cells A1 and B1 both text:
I want the resulting C1 value to the A1&B1 but initial Cap
Eg
A1=george
B1-smith
C1 would = George Smith
What formaula dow i need to put in C1 and thus be able to drag down the page to convert over 1000 rows
View 9 Replies
View Related
Feb 11, 2009
If I need to replicate a formula in some cells, I usually drag it down or sideways. Is there any easier way to do this? I have large numbers of cells into which to drag the formula. Can I specify which cell range the formula is applicable to (i.e. A1:A10,000 or something) instead of dragging? If so how?
View 5 Replies
View Related
Feb 28, 2013
Actually i can do it just by using LOOKUP formula but the mark scheme says LOOKUP and IF.
If i use Just LOOKUP i should do the same thing for other cells but i want to generate a formula and replicate it through all the cells.
Here is the question;
Enter formulae in cells C2:C10 that refer to Book Orders.CSV and display whether the Text Book is Required by the student whose Student id is entered in cell A2.
I also uploaded the source file. Q3.xlsx
View 2 Replies
View Related
Jun 7, 2007
I have two worksheets in the same workbook. In Sheet1, I've got a whole bunch of stuff that I need listed, like names, addresses, phone numbers, etc. What I want to do is pull the values of only the names, which are listed every 42 spots, and put them into a list on Sheet2. So, for example, in Sheet1, A1 is a name, A43 is a name, A85 is a name, etc. In Sheet2, I want to list only the names. And if there isn't a name yet, I want a blank cell. I've sorta figured out how to do it, but I get a problem with the values not incrementing correctly.
So, example code would be
=IF(ISBLANK(SHEET1!A1),"",A1)
=IF(ISBLANK(SHEET1!A43),"",A43)
etc.
The problem is when I drag the auto fill, the next values it gives are
=IF(ISBLANK(SHEET1!A3),"",A3)
=IF(ISBLANK(SHEET1!A44),"",A44)
Instead of the
=IF(ISBLANK(SHEET1!A85),"",A85)
=IF(ISBLANK(SHEET1!A127)"",A127)
....
that I want it to give.
is there anyway to get the values to go up like I want them to, or do I have to enter them all individually? That would really not be fun for me, so I hope that's not the case.
View 4 Replies
View Related
Feb 17, 2010
Im trying to make a formula that lets you type in your current level, and then calculates how much XP you have remaning untill you reach the max level.
The way the forumla would need to work would be something like this:
(E10) Enter Current Level:
(F10) 37 (which is my current level)
F11 - would then add cells from C37 to C70 (calculating the total amount of XP required) and then display in (F11)
Being able to say if, then is far to cpmplicated for me in excel. I'm just trying to make this for a bit of fun and am sure one of you talented people can easily do this.
View 4 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
Oct 24, 2007
I've a workbook with 3 tabs with names "tab1, tab2, difference" and I've a macro that calculates tab1-tab2 and puts the results in tab "difference" This macro runs every time I open the workbook or every time dataset is refreshed on any tab.
everything works great but i've following issue.
let's say my cursor is in tab2 & i refresh/update dataset on tab2 which will trigger macro to update values in tab "difference".. this is good. but, soon after macro runs & updates tab "difference", cursor is no more in tab2 but in tab "difference" this is OK if I've 1 or 2 or few tabs but i've 25 tabs & the user has to go back to the initial tab everytime dataset is updated on any tab. Is there a way for the macro to recognize where the cursor initiallly was & run the macro & place the cursor back to the initial tab?
View 2 Replies
View Related
Aug 28, 2012
I have a listing with Middle Initials in column D. D also contains dates and Names. I want to remove the Middle Initials only. I need to do this without moving around cells. So a Find:="A", Replacement:="" type of situation. Right now I have 26 two line entries to take care of this, but I know it has to be easier and use less lines. (Trying to consolidate code for a better look).
Here is some of what I have (that works, but is long):
Code:
'
' Replace Middle Initial from Prod Sheet
'
Cells.Replace What:="A", Replacement:="", LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
[Code]...
I thought that I might be able to do something like this, but I can't get it to work.
Code:
Dim Alpha as Variant
Alpha = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z")
For ABet = 1 to Cells(Rows.Count, "D").End(xlUP).Row
If Range(ABet) = Alpha Then Range(ABet) = ""
Next ABet
' or I thought maybe this would work.
Cells.Replace What:=("A", "L", "R", "M") Replacement:="", LookAt:=xlWhole, SearchOrder _ :=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
' or I thought maybe this would work with the variables.
If Range("B" & ABet) = Alpha Then Range("A" & ABet) = "" None of them worked though.
View 2 Replies
View Related
May 4, 2009
I have the following formula that I would like to be able to autofill.
=UniqueItem('Divide Outs'!$A$2:$A$999,1)
The 1 after the comma needs to count up...anyway to autofill a formula like this?
This is a function that i use to display unique listings.
View 3 Replies
View Related
Mar 29, 2007
I have data in colA. In colB I want to auto fill a formula that is in cell B1 all the way down and stop when the data in A stops.
Range("A1").AutoFill Destination:=Range("B1:B" & Range("B1").End(xlDown).Row)
It doesnt seem to like this though and I think ive got the cols all wrong in the formula.
View 4 Replies
View Related
Jul 18, 2012
I have the macro to insert a formula and copy it in a range("A2:A14", but I want the end of the range in column A to be the last row with data in column B. How to add that in?
Sub vlookup()
Range("A2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[1],'[Old.xlsx]Total Student Count Data'!R2C2:R1000C2,1,FALSE)"
Range("A2").Select
Selection.autofill Destination:=Range("A2:A14"), Type:=xlFillDefault
Range("A2:A14").Select
End Sub
View 2 Replies
View Related
Aug 1, 2012
The code below inserts a formula into E2, copies it across to column O and then down to the last row. It works fine but if more columns are added obviously it would need to be changed to go up to column P for example.
How can I change this so that the formula will be copied across to the last column automatically and therefore the code will not need to be changed?
Code:
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("E2").Select
[Code]....
View 2 Replies
View Related
Jan 4, 2008
I am trying to use autofill over a range of cells.
I am using a Do While loop to find an empty column in row 6 incremented by 5 based on LFound. The starting value of LColumn is 9 and is increased based on and IsEmpty condition.
Do While LFound = False
LColumn = 9
If IsEmpty(Cells(6, LColumn).Value) = True Then
LFound = True
Else
LColumn = LColumn + 5
End If
Loop
Next I want use this column index to put an equation in a cell
Cells(15, LColumn).Formula = "='" & SheetName & "'!C103"
Last I want to autofill the 8 cells below the refrenced cell.
Cells(15, LColumn).Select
Selection.AutoFill Destination:=Range(Cells(15, LColumn) & ":" & Cells(23, LColumn)),
Type:=xlFillValues
But I can't get the autofill to work. I think it is because of my attempt at going from Cells() to Range(),
View 3 Replies
View Related
Mar 8, 2013
I'm trying to autofill "vertically" in a spreadsheet using a formula which loads value from a different sheet.
Lets say I have cell (A1 for example) ='2013'!A39
Now I want to fill A2-A30 in a way that the formula updates to ='2013'!B39, ='2013'!C39, ='2013'!D39...and so on
I've tried using ='2013'!A$39, but this only copies the formula as it is. When I try to autofill cell A2-A30, the formula still is ='2013!A$39
Is this possible to do?
View 1 Replies
View Related
Jul 3, 2009
I have an xls with over 500 rows of data, every day I have to update the contents of some of the cells, Cell A contains the date and is auto filled already to the end of 2009, Cell B shows me the number of days since I began the sheet and is also auto filled already to the end of 2009, Cell C & Cell D I have to manually enter data
Cell E contains this formula =D527-D526
Cell F =C527/B526
Cell G = =IF(C527=0,0,C527-C526)
Cell H resorts to manual entry.
My question is "why do these columns with formulas, (E,F & G) not automatically carry the formula to the next row?" I'm sure that they once did. Is it a setting that I can't find?
This is excel 2007.
View 6 Replies
View Related
Oct 3, 2007
need to autofill collum C with a formula related do collum B. If I do this by hand I just type the formula in C 1, and click on the right-down corner of the cell selection, and the formula goes until the last line (last value on collum B). But I want to create a macro to this function, and the problem is that the files that I will apply the macro have different lengths. I want to modify the macro to be able to run from C1 until the end of the values on collum B. I don't want to freeze the last value.
Here is the macro
Sub Macro3()
ActiveCell.FormulaR1C1 = "=60000/RC[-1]"
Range("C1").Select
Selection.AutoFill Destination:=Range("C1:C4819")
Range("C1:C4819").Select
Range("E7").Select
End Sub
In other files C4819 will not be the last value, could be 5345 for example, but its impossible to do this, my macro should do: "autofill collum C with a formula until the last value on collum B"
View 9 Replies
View Related
Dec 9, 2013
I am trying to auto fill this formula down a column but it doesn't keep the C4,D4,E4...ETC to stay constant
I manually did these two correct ones
=SUM(C5*C4+D5*D4+E5*E4+F5*F4+G5*G4+H5*H4+I5*I4+J5*J4+K5*K4+L5*L4+M5*M4+N5*N4
+O5*O4+P5*P4+Q5*Q4+R5*R4+S5*S4+T5*T4+U5*U4+V5*V4+W5*W4+X5*X4+Y5*Y4+Z5*Z4+AA5*AA4)
=SUM(C6*C4+D6*D4+E6*E4+F6*F4+G6*G4+H6*H4+I6*I4+J6*J4+K6*K4+L6*L4+M6*M4+N6*N4
+O6*O4+P6*P4+Q6*Q4+R6*R4+S6*S4+T6*T4+U6*U4+V6*V4+W6*W4+X6*X4+Y6*Y4+Z6*Z4+AA6*AA4)
when I drag it down it incorrectly looks like this:
=SUM(C7*C6+D7*D6+E7*E6+F7*F6+G7*G6+H7*H6+I7*I6+J7*J6+K7*K6+L7*L6+M7*M6+N7*N6
+O7*O6+P7*P6+Q7*Q6+R7*R6+S7*S6+T7*T6+U7*U6+V7*V6+W7*W6+X7*X6+Y7*Y6+Z7*Z6+AA7*AA6)
I want C6,D6,E6 to be C4,D4,E4 ETC
View 3 Replies
View Related
Jan 20, 2008
Here is my test example I am trying to figure this out with.
Sub a()
Dim rg As Range
'set formulas to be filled
Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=IF('sheet2'!RC="""","""",'sheet2'!RC)"
Selection.AutoFill Destination:=Range("A1:L1"), Type:=xlFillDefault
'now get autofill to populate until no more data on sheet 2
Set rg = [a2]
Set rg = Range(rg, Cells(Rows.Count, rg.Column).End(xlUp))
rg.Cells(2, 2).AutoFill Destination:=rg, Type:=xlFillDefault
End Sub
Without VBA, once I set the formulas for A1:L1, I can use the mouse to autofill all columns until Im around row 27,000.
Id like to be able to run a check for the end of data in sheet 2 so that if the sheet2 changes, it will all be included.
The resulting sheet1 cannot have any null values, so a perfect end of data check is necessary.
View 9 Replies
View Related
Feb 15, 2009
I have a workbook with ten sheets. The front sheet is a summary with nine divisional data sheets (DDS) following. In every sheet cell G12 has the date. At the moment the DDS reference the summary sheet and pick up the same date from it. Is it possible to set it up so that if I change the date in cell G12 on any of the sheets I can get them to all read the same?
View 11 Replies
View Related
May 30, 2007
I'm exploring a different approach to my current project.
Sheet1, CellA1 contains the year ie. 2007
Sheet1, CellA2 contains the storage directory ie. E:archiveyear
Sheet1, CellA3:A10 contains the names of the employees.
Here is what I would like to make happen.
Running this in a macro, the result would be a copy of Workbook2 renamed for each employee's name in cells A3 through A10 and all saved in a newly created directory of
E:archiveyear2007employee name1.xls
E:archiveyear2007employee name2.xls and so on..
View 9 Replies
View Related
Aug 15, 2012
I have a userform that has nested multipages (5 in the outer page, 4 in the inner page).
On each of these multipages, I want to have the same controls (sliders) laid out in the same order - but with unique names, named after their tab location, for each control so I can use their value property later in the code.
I've designed the layout and named all the controls on my first sheet (e.g. Slider1Outer1Inner1). I now need a way of automating the replication of these across the other 19 sheets (including the nested inner multipage!); so that equivalent slider for example would be called Slider1Outer1Inner2, Slider1Outer1Inner3 etc.
I'm not sure about coding VBA to act on items within VBA...
VB:
" For each multipage in outer
For Each multipage In inner
For Each Object In current multipage
Copy inner.object -> Next multipage
Inner.object.name = CurrentOuter & CurrentInner & CurrentSlider "
View 2 Replies
View Related
Jul 24, 2007
if i create a record in 'XYZ' worksheet, it should create the same record in TARGET worksheet.
Likewise, if i create a record in 'ABC' worksheet in the same workbook, it should create the same record in the SUBSEQUENT ROW of the TARGET worksheet.
For this to happen, it must be done programmitically either using a MACRO or VB Script.
View 9 Replies
View Related
Dec 16, 2009
I understand the end(x1Down), but i think I am missing something. I just want to go to the next cell left, right or Up, Down from whatever cell is currently selected. How do I replicate a 1 cell move as an arrow key would do?
View 9 Replies
View Related
Dec 15, 2007
I want to create a list from a "seed" word that consists of a word followed by a number (ex: seedword100).
What I want to do is to be able to create a list in Excel using that seed word and then have excel produce a bunch of replications of that seed word by incrementing the number part of the seed word by 1 for every replication.
So, for example, if cell A1 = "seedword100", I want Excel to replicate it up to "seedword199" like this...
B1 = seedword101
C1 = seedword102
D1 = seedword103
E1 = seedword104
F1 = seedword105
G1 = seedword106
H1 = seedword107 ...
and so on until the last cell = seedword199 (or whatever number I want it so stop at).
View 7 Replies
View Related
Jun 14, 2013
So I have a need to enter multi-line notes. Using a merged cell not working for me so thinking best to use a Textbox.
This works fine for entering the notes but I need to replicate the content on another Tab in the same Excel workbook.
So lets say I have named my Texboxes 'Textbox1' on one tab, and 'Textbox2' on another tab...... how do I replicate the contents?
I've tried putting =Textbox1 into 'Textbox2' like you would for a normal Cell but that does not work.
View 5 Replies
View Related
Jun 16, 2009
I created a UserForm to populate client billing info and job location info (so it's a two-parter). A clicked button will then populate a spreadsheet with all of the information. That part works fine.
When the client billing info is the same as their job info, I'd like to offer a checkbox that will populate the job info with the billing info rather than having to type everything twice (like you see on online billing and shipping orders).
View 5 Replies
View Related
Apr 21, 2007
I am trying to rebuilt a chartsheet without using copy/paste. I have chartsheets with 2 or more charts on it. I add a new chartsheet, then add a new chartobject and then I want to copy the same series ( name, xyvalues, values). Unfortunately it doesn't work.
Sub ReplicateCharts()
Dim Cht As Chart
Dim NewCht As Chart
Dim ChtObj, NewChtobj As ChartObject
Dim SrSerie As Series
Dim ns As Series
Dim x As Variant
If ActiveWorkbook.Charts.Count > 0 Then
For Each Cht In .Charts
Set NewCht = .Charts.Add()
NewCht.PageSetup.Orientation = Cht.PageSetup.Orientation
For Each ChtObj In Cht.ChartObjects...........................
View 8 Replies
View Related
Sep 2, 2013
I am using Windows 7, with Excel 2010.
I have one Worksheet Short Course - PB's Which contains all swimmer information and searches through all previous swims and reports back the swimmers current Personal best times (PB)
I am creating a work sheet to calculate percentage increases over a set date period. I have managed the calculations but can not get the autofill to function as I was hoping.
In my short course sheet 1 Row = a Swimmer and there details
In my new sheet, I have 3 rows for the same swimmer
Row 1= Swimmer and PB's before a set date
Row 2 = Swimmer and PB's After set date and upto Todays date
Row 3 = Percentage calculation of difference between the two rows to enable track performance increase
I have all of this working and in place and want to copy the formula's down now to cover all swimmers in the club.
When I copy the 3 lines down, Autofill adds 3 to the row reference for the first line and I just want it to add 1.
ROW3) =IF('Short Course - PB''S'!A3="","",'Short Course - PB''S'!A3)
ROW4) =A3
ROW5) = A3
[Code].....
View 4 Replies
View Related
