Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
leave cells blank until formula applied | Excel Worksheet Functions | |||
how do you force a new line in a formula in Excel? | Excel Worksheet Functions | |||
blank cell until after formula applied | Excel Discussion (Misc queries) | |||
How do I define colors that are automatically applied to data seri | Charts and Charting in Excel | |||
Validation applied to formula result | Excel Discussion (Misc queries) |