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

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
>

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

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" <(E-Mail Removed)> wrote in message
news:348BDE8A-7BA7-4DE5-B7CD-(E-Mail Removed)...
> 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
>>

On Wed, 12 Mar 2008 03:33:23 -0700, Gary''s Student
<(E-Mail Removed)> 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

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
> <(E-Mail Removed)> 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
>