copying formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alex - 25 Oct 2006 10:57 GMT

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

Reply to this Message

Roger Govier - 25 Oct 2006 11:18 GMT

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.

Signature

Regards

Roger Govier

> Hi
>
[quoted text clipped - 7 lines]
> Thanks
> A

Reply to this Message

vezerid - 25 Oct 2006 11:25 GMT

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

> Hi
>
[quoted text clipped - 6 lines]
> Thanks
> A

Reply to this Message

Alex - 25 Oct 2006 11:49 GMT

Thanks Both

> Hi
>
[quoted text clipped - 7 lines]
> Thanks
> A

Reply to this Message