can I force formula to be applied automatically?
 

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

can I force formula to be applied automatically?
 

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
--
Gary''s Student - gsnu200773


"DAXU" wrote:

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

can I force formula to be applied automatically?
 

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

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200773


"DAXU" wrote:

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

can I force formula to be applied automatically?
 

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

--
HTH :)
Dana DeLouis


"Gary''s Student" wrote in message
...
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
--
Gary''s Student - gsnu200773


"DAXU" wrote:

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

can I force formula to be applied automatically?
 

On Wed, 12 Mar 2008 03:33:23 -0700, Gary''s Student
wrote:

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

can I force formula to be applied automatically?
 

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)



"Ron Rosenfeld" wrote:

On Wed, 12 Mar 2008 03:33:23 -0700, Gary''s Student
wrote:

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