Tags: autofill, column, excel, formula, formulae, microsoft, msdn, newbie, programmer, software, worksheet, writing

"autofill" column with formula.

On Microsoft » Microsoft Excel

5,907 words with 3 Comments; publish: Thu, 22 May 2008 05:22:00 GMT; (30662.01, « »)

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

All Comments

Leave a comment...

  • 3 Comments
    • 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.

      In article <c6189f16.0408261907.7c75177e.excel.todaysummary.com.posting.google.com>,

      garrodoran.excel.todaysummary.com.hotmail.com (Seabook) wrote:

      > 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?

      #1; Thu, 22 May 2008 05:23:00 GMT
    • 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

      --

      HTH,

      David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]

      My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

      Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

      "JE McGimpsey" <jemcgimpsey.excel.todaysummary.com.mvps.org> wrote ...

      > 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.

      > garrodoran.excel.todaysummary.com.hotmail.com (Seabook) wrote:

      > > 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?

      #2; Thu, 22 May 2008 05:24:00 GMT
    • Well, new to WinXL. It's been in MacXL since 2001.

      In article <OVd76UGjEHA.3612.excel.todaysummary.com.TK2MSFTNGP12.phx.gbl>,

      "David McRitchie" <dmcritchie.excel.todaysummary.com.msn.com> wrote:

      > I guess List Manager is new in Excel 2003 at least it looked

      > like that was implied.

      #3; Thu, 22 May 2008 05:25:00 GMT