Automatically fill down formulas when adding a record

  • Thread starter Thread starter steveski
  • Start date Start date
S

steveski

I have a data entry sheet which receives one record per row. Some of th
columns contain calculated fields and will be hidden. Is there a way t
have the formulas automatically fill down when new records are added
Thank you
 
One way is to use Excels built-in dataform.
data>form


--
John
johnf 202 at hotmail dot com


| I have a data entry sheet which receives one record per row. Some of the
| columns contain calculated fields and will be hidden. Is there a way to
| have the formulas automatically fill down when new records are added?
| Thank you.
|
|
| ---
| Message posted
|
 
Hi
one way:
use a formula like: =IF(A1<>"",your_formula,"")
and copy this down. Your formula will only be evaluated if column a
contains a value

another way: Use the worksheet change event to check for entries in one
column (lets say column A) and add a formula in column B if a value is
entered in column A:

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 CleanUp:
With Target
If .Value <> "" Then
Application.EnableEvents = False
.Offset(0, 1).FormulaR1C1 = "=RC[-1]*5" ' enter your
formula
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

Frank
 
I use a macro I recorded to Copy a blank row (with formulas) I set in at the
top of the sheet just for this purpose..........

hth
Vaya con Dios,
Chuck, CABGx3
 
Steveski,

Excel will copy formulas down as you add records. I think there must be at
least 5 records already there. Be sure you have "Extend list formats and
formulas" checked in Tools - Options - Edit. It works when using Data -
Form to add records too.

If you have a formula at the bottom (such as SUM), you'll have to insert a
new row yourself for the record to be added. When you enter the new record,
the SUM formula will get its extents increased to include the new row, and
formulas in the prior record will be copied down.
 
Back
Top