Automating formula copying

  • Thread starter Thread starter stu
  • Start date Start date
S

stu

Is it possible to have Excel automatically duplicate a
formula in one column when a new entry is made in another
column? As a new line is typed I want the formula to drop
down without having to manually copy it.
Thanks in advance.
 
Hi
goto 'tools - Options - Edit' and check the upper right option (should
be something like 'extend formulas')
 
You might also want to take a look at Data / Form, as this will automate the
whole process of adding records and formulas for you. Name the range 'Database'
and it will work better, and will expand automatically as records are added via
Data / Form.
 
I was looking for a way that would by-pass the need to
extend the formula in the column. Is it possible to
format a column for a formula? The way that has been used
was to extend the formula, =IF(A21="","",G20+E21-F21),
for the first say 100 rows but eventually someone will
run out of copied formulae (?sp) and the extending
process will have to be repeated.
Ken's solution is good but would add step in training for
the people using the sheet.
Thanks in advance for the help.
 
Hi
you could add an event procedure to your worksheet that will put a
formula into your column after you have entered something in column A.
Try the following code (it has to go in your worksheet module, not in a
stadard module)
I assumed that the formula should go in column G:

---
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub
on error goto errhandler
If Target.Value <> "" Then
application.enableevents = false
target.offset(0,6).formulaR1C1="=R[-1]C7+R[0]C5-R[0]C6"
End If
errhandler:
application.enableevents = True
End Sub
 
That did the trick! I hadn't thought of programing the
sheet itself. I've made a template and just create new
sheets as I need them.
Thanks again, Frank.

-----Original Message-----
Hi
you could add an event procedure to your worksheet that will put a
formula into your column after you have entered something in column A.
Try the following code (it has to go in your worksheet module, not in a
stadard module)
I assumed that the formula should go in column G:

---
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub
on error goto errhandler
If Target.Value <> "" Then
application.enableevents = false
target.offset(0,6).formulaR1C1="=R[-1]C7+R[0]C5-R [0]C6"
End If
errhandler:
application.enableevents = True
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany

I was looking for a way that would by-pass the need to
extend the formula in the column. Is it possible to
format a column for a formula? The way that has been used
was to extend the formula, =IF(A21="","",G20+E21-F21),
for the first say 100 rows but eventually someone will
run out of copied formulae (?sp) and the extending
process will have to be repeated.
Ken's solution is good but would add step in training for
the people using the sheet.
Thanks in advance for the help. right
option (should
Click to expand...

.
Click to expand...
 
Back
Top