can I force formula to be applied automatically?

[DAXU]

Hi,

For example, I have a excel sheet with two columns, A and B.
B's value is calculated based on A, e.g. =A1+1


My problem is that the formula is only applied to the cells selected.
I have to drag it to apply for other cells.
Is there a way that when new cell is added to column A, column B will
be automatically populated?

Many Thanks

Jerry

 

[Gary''s Student]

There are two ways, EASY and HARD:

EASY:

Fill column B with:
=IF(A1="","",A1+1) and fill way down. As data get added in column A the
results appear in column B.

HARD:

Install this small worksheet event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set a = Range("A:A")
If Intersect(t, a) Is Nothing Then Exit Sub
If IsEmpty(t.Offset(0, 1).Value) Then
Application.EnableEvents = False
t.Offset(0, 1).Formula = "=A" & t.Row & "+1"
Application.EnableEvents = True
End If
End Sub

 

[Joel]

Gary's code won't work if you are copying and pasting multiple rows. try
this instead

Private Sub Worksheet_Change(ByVal Target As Range)
for each cell in Target
if cell.column = 1 then
If IsEmpty(cell.Offset(0, 1).Value) Then
Application.EnableEvents = False
cell.Offset(0, 1).Formula = "=A" & cell.Row & "+1"
Application.EnableEvents = True
end if
End If
next cell
End Sub

 

[Dana DeLouis]

In Excel 2007, another way is to give a header in A1 & B1.
Select say A1:B10, and make these a "Table" via Insert > Table.
Add data in Column A.
In B2, add a Formula, and it will fill in adjacent to your data in A.
As you add data in A, the equation is automaticllly applied in B

 

[Ron Rosenfeld]

There are two ways, EASY and HARD:

Gary's student:

There is another, even easier way, but durned if I can figure it out
completely.

Excel 2002: In one of my workbooks, in two columns of that workbook (but not
in others that also have formulas), formulas automagically get extended down
when I put a new entry into the left hand column.

I've not looked into how or why that occurs in those columns and not in others.

Under Tools/Options/Edit there is a selection item:

Extend list formats and formulas.

which is supposed to do this sort of thing.
--ron

 

[Patrick Molloy]

an even easier way is to use the worksheet OFFSET function.

so if the data alone starts at sat A10 then in any other cell, say C10 put
this:
=OFFSET(A10,COUNTA(A:A)-1,,1)