copying formula

A

Alex

Hi

I use the following to test the last character in a cell.
=IF(RIGHT(E4,1)="A","Yes","No").

When I add more rows, I am always forgetting to copy the formula down. is
there anyway that i can set up the worksheet so that I dont have to remember

Thanks
A
 
R

Roger Govier

Hi Alex

If you have Excel 2003, then use Data>List to create your table.
Then as you add new rows, the formulae will automatically get added.
 
V

vezerid

Alex,

you can achieve this using a VBA event procedure on the change event.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 And Target.Cells.Count = 1 Then
Target.Offset(0, 1).FormulaR1C1 = Cells(4, 6).FormulaR1C1
End If
End Sub

This procedure assumes that you are building your formula in column F:F
and that the first formula that you want to copy is in F4. It will fire
whenever you make a change in a cell in column E:E and it will copy the
formula of F4 into the cell right of the recently changed cell.

To install:
Right-click the sheet tab where you are building your data/formulas.
Choose Edit Code.
Paste the above code in the window that comes up.

HTH
Kostis Vezerides
 
Top