copying formula

[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

 

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

 

[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

 

[Alex]

Thanks Both