"autofill" column with formula.

[Seabook]

Hello,
I'm a programmer, but I'm an Excel newbie. Here is my problem: I'm
writing a worksheet with a few formulae and I want each formula to be
somehow autofilled in the whole column as I fill the rows. For
example, I have columns A, B, and C, like the following:
| A | B | C |
1 | Principal | Interest | Balance |
2 | 10000| 16| 10016|
3 | 20000| 28| 20028|

Row 1 is a frozed pane
and C2=$A2+$B2
C3=$A3+$B3

I want it so that, as I fill the columns A and B of the subsequent
rows, column C is automatically calculated immediately, WITHOUT me
manually autofilling the column using the autofill handle. Is it
possible? And how do I do that?

Cheers,
Kal

 

[JE McGimpsey]

If you're using MacXL, or WinXL03, use the List Manager (it's not called
List Manager in XL03 - look up "Create a List" in XL Help), and set
column C to be a calculated column. Whenever you add a value in the
input row, the formula will be entered in column C.

 

[David McRitchie]

Hi Kal,
I guess List Manager is new in Excel 2003 at least it looked
like that was implied.

I'd use an change event macro
http://www.mvps.org/dmcritchie/excel/excel.htm

Seems strange that you have a balance not related
to a previous row.

Anyway from what you posted the following should work.

Private Sub Worksheet_Change(ByVal Target As Range)
'to install -- right-click on the sheettab of the sheet to
' be used in and choose 'view code'. Paste this Worksheet
' event macro into the module.
If Target.Column > 2 Then Exit Sub
If Target.Row = 1 Then Exit Sub
If IsEmpty(Target) Then Exit Sub
Cells(Target.Row, 3).Formula = "=" _
& Cells(Target.Row, 1).Address(0, 0) _
& " + " & Cells(Target.Row, 2).Address(0, 0)
End Sub

Exit immediately if update is to row 1 or a column
other than A or B (column 1 or 2).

Don't update formula in column C, if the cell is column
A or B is cleared out as the update.

Create a formula equivalent to C2: =A2 + B2

If you really want C2: =$A2 + $B2
then use the following:
Cells(Target.Row, 3).Formula = "=" _
& Cells(Target.Row, 1).Address(0, 1) _
& " + " & Cells(Target.Row, 2).Address(0, 1)

following instructions to install seen within macro.

You can add the formula if not present by
selecting cell B2, then PF2 then enter.
Then drag down using the fill handle (buldge) on C2
down as far as needed. If you actually have something
in column B you can double-click on the fill handle.

Any updates later on should automatically cause
formula to be created in Column C.

As indicated previously
Seems strange that you have a balance not related
to a previous row. You might also take a look at a
checkbook type of balance in
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

 

[JE McGimpsey]

Well, new to WinXL. It's been in MacXL since 2001.