### 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; (30693.75, « »)

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

*http://excel.questionfor.info/q_microsoft-excel_218.html*

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.questionfor.info.posting.google.com>,

garrodoran.excel.questionfor.info.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

- If you're using MacXL, or WinXL03, use the List Manager (it's not called
- 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.questionfor.info.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.questionfor.info.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

- Hi Kal,
- Well, new to WinXL. It's been in MacXL since 2001.
In article <OVd76UGjEHA.3612.excel.questionfor.info.TK2MSFTNGP12.phx.gbl>,

"David McRitchie" <dmcritchie.excel.questionfor.info.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

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